Database efficiency with generating tables. (1 Viewer)

Raymas

Registered User.
Local time
Tomorrow, 00:04
Joined
Mar 8, 2011
Messages
21
Hi all,

I have been working on product database since the start of the year. I had never used MS Access before, and I have leant a great deal. But this is where I begin to doubt my abilities, as I have taught myself mostly everything I know.

I am just wondering will I the end user see noticable lag in the database when it is at capacity, if I regenerate many tables on some functions? I am seeing this being an issue when cascading updates occur. For instance, at the far end of the scale; if I update the currency exchange rates, this will regenerate the cost of raw materials in the products, which will update the all the product total costs, which will update all the prices for all the products. There is a mix of completely regenerating tables and just updating specific parts of tables. But if in the worst case scenario, all tables were regenerated due to a small change, would the end user experience any noticeable lag?

There are upto 5000 records in some of the tables, and upto 5/6 fields in each. At what sort of capacity or workload does access appear to lag? I'm just thinking back to high school, when some programmes would take forever to update or perform a certain task (or even photoshop on low end systems these days, haha), and really want to avoid this for the end user.
 

bparkinson

Registered User.
Local time
Today, 07:04
Joined
Nov 13, 2010
Messages
158
Avoid the issue entirly. Don't persist derived/calculated values like the cost of raw materials. Instead, calculate them on the fly in a query as needed.

The last thing you want to do is multiply the cost of a part by the quantity estimated for the job and store that.

This design can never scale, and performance could well be terrible.
 

Raymas

Registered User.
Local time
Tomorrow, 00:04
Joined
Mar 8, 2011
Messages
21
Thanks for the reply. I see what you are saying to an extent. And I do follow this in my database to with temporary tables, that only store data based on the current product being developed. But the results, ie the total costs, end pricing etc needs to be stored somewhere. As I have reports that show all this data for the end user to view, and it would be highly impractical for the database to calculate the costs off the bill of materials and prices for every product, when generating this report.
I think though from what you said, rather than updating all the costs/prices for every product every time a raw material cost is updated, I will rather update these when outputing the report, as this is the only real case when all this information is seen together.
If you have any pro's or con's for this direction, I'm all ears, as I really would like to learn to design my database better.

Cheers,

Raymas
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:04
Joined
Sep 12, 2006
Messages
15,613
it depends how many prices you are talking about.

in general terms, as bp says, you should not need to recalculate prices, when a base price changes, because you should not be storing the calculated value.

now you might want to break this rule in some cases, but you need to understand all the implications of denormalising the data.

talking about regenerating tables, cascading updates etc, all points to non optimal design, if you will
 

Raymas

Registered User.
Local time
Tomorrow, 00:04
Joined
Mar 8, 2011
Messages
21
So just to make clear what i'm trying to achieve. Basically the database is made up of products, raw materials, and raw materials costs. The products each have a bill of materials (BOM) generated by the user. The database then from this BOM generates a total product cost (also including labour costs, overhead etc which I won't go into). From this total cost it generates 3 prices for various customers. These are not the set prices used by the company, but more of guide. When accounts update raw material costs, or any other costs due to inflation etc, this needs to update the costs and prices (otherwise the information outputed is out of date, and misinforming). These are viewed in reports either individually, or the entire product catalog (which would mean 1 cost and 3 prices for 2000+ products).
So i'm guessing if these are not stored in the database, it would mean generating 8000 calculations for the collective form, am I right? Every way I think of solving this one seem inefficient...
For the individual reports, it is easy enough to generate the calculations for each case.
 

bparkinson

Registered User.
Local time
Today, 07:04
Joined
Nov 13, 2010
Messages
158
OK, I get it now. A product is made up of raw materials. For example, a floor might be made from one of several different raw materials, perhaps pergo, or a wood laminate, but never both. Each raw material can have a different price. I've done this before. Here's my suggestion.

You have a product table. You have a raw materials table. There is a control table that expresses which raw materials are candidates for a particular product (an assembly, in my mind). That way, you can segment out a small list of raw materials at data entry time that are selectable from a combo box (maybe). There is a RawMaterialsPrice table. It contains as many prices as you wish for each raw material. At BOM data entry time, the user can apply a discount to the raw material price (one price is maybe the default, or prices are associated with customer types). The discount can be positive or negative, allowing infinite variability in the price, which it sounds like you need.

What to persist in the BOM object? Raw material price base price (whichever the user selected, or was proposed by the relationship between price and customer type (or other customer attribute)), discount, raw material quantity. You can easily derive your cost data from that.

This is really just an order entry system, where the BOM is the order, the raw materials are the order items, and the status of the order is a quote.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:04
Joined
Sep 12, 2006
Messages
15,613
So just to make clear what i'm trying to achieve. Basically the database is made up of products, raw materials, and raw materials costs. The products each have a bill of materials (BOM) generated by the user. The database then from this BOM generates a total product cost (also including labour costs, overhead etc which I won't go into). From this total cost it generates 3 prices for various customers. These are not the set prices used by the company, but more of guide. When accounts update raw material costs, or any other costs due to inflation etc, this needs to update the costs and prices (otherwise the information outputed is out of date, and misinforming). These are viewed in reports either individually, or the entire product catalog (which would mean 1 cost and 3 prices for 2000+ products).
So i'm guessing if these are not stored in the database, it would mean generating 8000 calculations for the collective form, am I right? Every way I think of solving this one seem inefficient...
For the individual reports, it is easy enough to generate the calculations for each case.

no - this is just the point. 8000 calculations is a trivial number of transactions for a pc to process. given an average pc that approaches 1GFLOPS in terms of raw speed.

this is preferable to the potential for confusion caused when your BOM calculations fail somehow, and your calcluated tables do not agree with the underlying data - and you have no way of checking this. hence the circumstances in which you elect to calculate and store data that can otherwise be calculated on demand must be carefully evaluated.
 

Raymas

Registered User.
Local time
Tomorrow, 00:04
Joined
Mar 8, 2011
Messages
21
Thanks for your replys. Sorry I've been so late getting back, I've just been assigned other tasks in the last few days that were more important.
Ok, so bp, what you have decribed is basically what I have done, although mine is little more complicated, ie I have many suppliers for one material, with primary supplier selected for each material to derive raw material costs from etc.

I hope I'm not wasting your time being overly descriptive, but I feel if I lay it out, I can get the best feedback. At the base level I have as you described, a products table (with all the product details), a raw materials table and a supplier table. A cross table between materials and suppliers gives the cost of each material per quantity. Another cross table between the materials cost table and the products called a parts list table, specifies the quantities of raw materials in the product (different materials are calculated in a different manner, depending on what they are, so these are calculated and appended to a "product/materials cost" table), I also have tables which calculate labour costs (based on many inputs) and overhead costs. These are then totalled into a "total product cost" table, and also from this three prices are calculated for our three different sales areas.

Several report types can be generated from these inputs:
A complete list of products with most details including costs and prices
Individual reports on each product with every detail including costs and prices
Complete materials list including all supplier costs (which also can be viewed in different currencies, using another table that has the conversion rates)
and a few other reports, which show the material pick lists and seperate material costs that go into a product.

Now I understand what you are saying, in that these calculations should be calculated on demand when data is retreived through reports. They still need to be stored in tables in order for the reports to read them out, yes? So I don't really need to change the structure or my database, but rather move when the calculations occur?

If you have gotten this far into my response, thank you for your patience, and thanks in advance for any responses.

Cheers,

Raymas
 

bparkinson

Registered User.
Local time
Today, 07:04
Joined
Nov 13, 2010
Messages
158
Thanks for your replys. Sorry I've been so late getting back, I've just been assigned other tasks in the last few days that were more important.
Ok, so bp, what you have decribed is basically what I have done, although mine is little more complicated, ie I have many suppliers for one material, with primary supplier selected for each material to derive raw material costs from etc.

I hope I'm not wasting your time being overly descriptive, but I feel if I lay it out, I can get the best feedback. At the base level I have as you described, a products table (with all the product details), a raw materials table and a supplier table. A cross table between materials and suppliers gives the cost of each material per quantity. Another cross table between the materials cost table and the products called a parts list table, specifies the quantities of raw materials in the product (different materials are calculated in a different manner, depending on what they are, so these are calculated and appended to a "product/materials cost" table), I also have tables which calculate labour costs (based on many inputs) and overhead costs. These are then totalled into a "total product cost" table, and also from this three prices are calculated for our three different sales areas.

Several report types can be generated from these inputs:
A complete list of products with most details including costs and prices
Individual reports on each product with every detail including costs and prices
Complete materials list including all supplier costs (which also can be viewed in different currencies, using another table that has the conversion rates)
and a few other reports, which show the material pick lists and seperate material costs that go into a product.

Now I understand what you are saying, in that these calculations should be calculated on demand when data is retreived through reports. They still need to be stored in tables in order for the reports to read them out, yes? So I don't really need to change the structure or my database, but rather move when the calculations occur?

If you have gotten this far into my response, thank you for your patience, and thanks in advance for any responses.

Cheers,

Raymas

99% of the time, it is wrong to store a derived or calculated data in a table. Use a query. You can treat the query as a table, using it as a datasource for your reports, forms, etc.
 

Raymas

Registered User.
Local time
Tomorrow, 00:04
Joined
Mar 8, 2011
Messages
21
Oh ok, cheers.. that makes so much more sense now.

Many thanks,

Raymas
 

bparkinson

Registered User.
Local time
Today, 07:04
Joined
Nov 13, 2010
Messages
158
Oh ok, cheers.. that makes so much more sense now.

Many thanks,

Raymas

Glad to help. If you want a query to be updatable, in the case of using it on a forn, there are conditions it has to meet. I know them in SQL Server, but if you ever bind a query to a form and it won't allow update, you are probably missing a primary key from one of the tables.

Just saying for the sake of completeness. Wouldn't worry until it happens. If you are just making queries to create derived data for your reports it's not an issue.
 

Users who are viewing this thread

Top Bottom