Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-05-2011, 05:26 PM   #1
Raymas
Newly Registered User
 
Join Date: Mar 2011
Posts: 21
Thanks: 8
Thanked 0 Times in 0 Posts
Raymas is on a distinguished road
Database efficiency with generating tables.

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.

Raymas is offline   Reply With Quote
Old 10-06-2011, 03:44 PM   #2
bparkinson
Newly Registered User
 
Join Date: Nov 2010
Posts: 158
Thanks: 1
Thanked 19 Times in 19 Posts
bparkinson is on a distinguished road
Re: Database efficiency with generating tables.

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.
bparkinson is offline   Reply With Quote
Old 10-09-2011, 03:54 PM   #3
Raymas
Newly Registered User
 
Join Date: Mar 2011
Posts: 21
Thanks: 8
Thanked 0 Times in 0 Posts
Raymas is on a distinguished road
Re: Database efficiency with generating tables.

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

Raymas is offline   Reply With Quote
Old 10-09-2011, 04:49 PM   #4
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,791
Thanks: 55
Thanked 1,027 Times in 993 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Database efficiency with generating tables.

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
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 10-09-2011, 06:55 PM   #5
Raymas
Newly Registered User
 
Join Date: Mar 2011
Posts: 21
Thanks: 8
Thanked 0 Times in 0 Posts
Raymas is on a distinguished road
Re: Database efficiency with generating tables.

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.
Raymas is offline   Reply With Quote
Old 10-09-2011, 09:27 PM   #6
bparkinson
Newly Registered User
 
Join Date: Nov 2010
Posts: 158
Thanks: 1
Thanked 19 Times in 19 Posts
bparkinson is on a distinguished road
Re: Database efficiency with generating tables.

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.
bparkinson is offline   Reply With Quote
Old 10-10-2011, 12:50 AM   #7
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,791
Thanks: 55
Thanked 1,027 Times in 993 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Database efficiency with generating tables.

Quote:
Originally Posted by Raymas View Post
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.

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
The Following User Says Thank You to gemma-the-husky For This Useful Post:
Raymas (10-13-2011)
Old 10-13-2011, 04:53 PM   #8
Raymas
Newly Registered User
 
Join Date: Mar 2011
Posts: 21
Thanks: 8
Thanked 0 Times in 0 Posts
Raymas is on a distinguished road
Re: Database efficiency with generating tables.

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
Raymas is offline   Reply With Quote
Old 10-13-2011, 05:26 PM   #9
bparkinson
Newly Registered User
 
Join Date: Nov 2010
Posts: 158
Thanks: 1
Thanked 19 Times in 19 Posts
bparkinson is on a distinguished road
Re: Database efficiency with generating tables.

Quote:
Originally Posted by Raymas View Post
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.
bparkinson is offline   Reply With Quote
The Following User Says Thank You to bparkinson For This Useful Post:
Raymas (10-13-2011)
Old 10-13-2011, 07:42 PM   #10
Raymas
Newly Registered User
 
Join Date: Mar 2011
Posts: 21
Thanks: 8
Thanked 0 Times in 0 Posts
Raymas is on a distinguished road
Re: Database efficiency with generating tables.

Oh ok, cheers.. that makes so much more sense now.

Many thanks,

Raymas
Raymas is offline   Reply With Quote
Old 10-13-2011, 07:50 PM   #11
bparkinson
Newly Registered User
 
Join Date: Nov 2010
Posts: 158
Thanks: 1
Thanked 19 Times in 19 Posts
bparkinson is on a distinguished road
Re: Database efficiency with generating tables.

Quote:
Originally Posted by Raymas View Post
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.

bparkinson is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Across Two Tables Generating Odd Balls daygo140 Queries 7 06-09-2008 06:22 AM
Generating Powerpoint Slideshow w/Tables airforceruss Modules & VBA 6 11-21-2007 11:24 AM
Generating a query using 4 tables and totalling one of the columns :S HairyArse Queries 1 02-03-2006 06:47 AM
Generating Totally New Tables via Data Input tbaxter General 2 01-15-2003 12:15 PM
Generating reports from multiple tables Loriebob Reports 1 08-17-2001 04:18 PM




All times are GMT -8. The time now is 06:00 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World