Master list table with changing values.

[evander]

Hey guys. I wanna ask the pros around here what they think about my design. It's about master lists that contain columns of data that may change over time. Let me describe my problem:



I have a master table called tbmInventory. It's got the following fields.
-InventoryID (PK, Text)
-InventoryName (Text)

I have another master table called tbmInventoryPrice that will account for price changes that are specific to each InventoryID. It's got the following fields.
-InventoryID (Compound PK, Text, FK related to tbmInventory)
-DateID (Compound PK, Date/Time)
-InventoryPrice

I have a transaction table called tbtInventory. It's got the following fields (This table is actually normalized into a header transaction table and a detail transaction table, but I think we can omit this fact for this thread).
-TransactionID
-TransactionDate
-InventoryID (FK related to tbmInventory, not related to tbmInventoryPrice)
-InventoryPrice

My current approach to applying the correct price for a particular InventoryID based on TransactionDate is:
a. Encode values in TransactionDate and InventoryID in the tbtInventory's form.
b. A Form_AfterInsert event fires.
c. In the Form_AfterInsert event, I placed a code that will lookup the price of the InventoryID in the tbmInventoryPrice table. The code should find the correct price for the date that is closest to the transaction's date.


Now, I know that this is a glaring violation of normalization, which states that "calculations" should not be stored as static values in permanent table fields. While I can design a query that will dynamically display the correct prices for all InventoryID's (thereby eliminating the InventoryPrice field in tbtInventory), I nevertheless avoided this kind of design because I am aware that my database will soon get populated by numerous records and such query might make database performance suffer.

QUESTION: Can I maintain my current design, whereby InventoryPrice is looked up in the tbmInventoryPrice by VBA code, and placed as a permanent, static value in tbtInventory? Or is this a major database design flaw and am I missing anything?

Thanks guys and I'm hoping to get a reply.

[jzwp11]

First, I would have my primary key fields as autonumber or at least number fields. Having text fields as key fields is inefficient and will slow your queries. If you need to have a text field for the inventory number that is fine, just don't use it as your primary key field. I also have not found a situation where I needed composite keys.


With that said this is how I would structure the tables.

tbmInventory
-pkInventoryID primary key, autonumber
-InventoryID (Text)
-InventoryName (Text)

tbmInventoryPrice
-pkInventoryPriceID primary key, autonumber
-fkInventoryID (long number, FK to tbmInventory)
-dteEff (date/time field representing the effective date of the price change)
-InventoryPrice

I don't quite understand why you have transactiondate in your tbtInventory shouldn't it just reside in the header transaction table (assuming that for each header you might have multiple inventory transactions)? What are you actually tracking? Movements in and out of inventory? If so, where is your quantity field?


tblHTransaction(transaction header table)
-pkHTransID primary key, autonumber
-dteTrans (transaction date)

tbltInventory
-pkTInventoryID primary key, autonumber
-fkHTransID foreign key to tblHTransaction
-fkInventoryID foreign key to tbmInventory
-quantity

Since you can use a query to find the price of an inventory item for any time given the effective date in the price table, there is no need to store the price again in the tbltInventory.

[evander]

Thanks for your reply, pal.
This is actually an old post...
I got excited when someone finally noticed it.

I don't quite understand why you have transactiondate in your tbtInventory shouldn't it just reside in the header transaction table (assuming that for each header you might have multiple inventory transactions)? What are you actually tracking? Movements in and out of inventory? If so, where is your quantity field?

Yes, I did normalize the inventory transaction table into header transaction table (where you can find the transaction date), and a detail transaction table (where you can find the inventory items, because it is possible to transact multiple inventory items on a certain date).

Movements in and out of inventory? If so, where is your quantity field?

I do have a quantity field. However, I omitted this field in this post so as to put more emphasis on price changes.

Since you can use a query to find the price of an inventory item for any time given the effective date in the price table, there is no need to store the price again in the tbltInventory.

Yes you are right. It is quite easy to create a query that finds the correct price of an inventory item given a specific transaction date. I can do this either by creating a plain SQL (not using DLOOKUP, but by using subqueries) or by creating a custom VBA function.

My concern is, if this dynamic way of looking up prices may have a performance impact in the future:

tbmInventory (Inventory master list) contains at least 5,000 unique inventory items.

tbmInventoryPrice (Inventory price master list) contains at least 20 different price entries for one inventory item for a single year. Therefore, there are 100,000 unique entries for a year, and probably 500,000 in the next five years.

tbhInventory (Inventory header transaction) can contain at least 50,000 unique entries of transaction documents for a year.

tbdInventory (Inventory line item detail transaction) can contain at least 30 line item entries for each transaction document. Thus, if we merge tbhInventory and tbdInventory by using an inner join, we get 1,500,000 unique entries in a year.

I would like to hear your opinion regarding the volume of transactions and the impact of having a query that dynamically looks up the correct price for a particular transaction date.

I have seen popular systems that "denormalize" inventory price data - that is, those systems store price as "static" values in transaction tables. Even the perpetual average unit cost is stored as a "static" value, which is subject to further change every time a purchase is made.

Looking forward to your response. Thanks.

[pbaldy]

Personally I have no problem with storing the price in the transaction table. It could be argued (not that I want to start a normalization debate) that the price for any given transaction is an attribute of the transaction, not of the product, so it isn't necessarily "denormalized". Even if it is, as you've noted this is a situation where the rule is often broken.

[evander]

Thanks for the reply Paul!

It could be argued (not that I want to start a normalization debate) that the price for any given transaction is an attribute of the transaction, not of the product, so it isn't necessarily "denormalized".

I have never thought of this idea. Actually, I am not an IT graduate, so it would take an expert telling me this for me to realize it.

I created a routine in the form that prevents the user from editing the date when there are inventory items encoded already, so that the prices are still consistent with the effective date.

Do you have any other situations where normalization rules are broken? Can you point me to any good site? I think I can really benefit from your experience. I learned Access solely by experience and some books, and I'm having a hard time seeing how things work in the real world simply because I'm not even exposed to it.

Thanks Paul! Hope to get a reply from you again.

[pbaldy]

I am not an IT graduate, so it would take an expert telling me this for me to realize it.

You're still waiting then, because I'm no expert. I learned from books and sites like this, so there are big gaps in my knowledge. All my experience is in the real world, so I'm vulnerable to using what works, whether or not it's what's "right".

[evander]

I just saw the contents of your site a few minutes ago. I find your solutions practical. And I appreciate the way you responded to my question because your answer is practical. I wanted to know if there are people out there who have been doing the same, or who would do the same approach as I did, because I rely more on what solutions work for me that others have already come up with before.

May I browse your site? I found an interesting topic about cascading combo boxes and I wanna implement that in my next project.

Thanks Paul!

Joe

[pbaldy]

May I browse your site?

Of course! That's what it's there for. I hope it helps you out.

[jzwp11]

I probably am not the right person to answer your question about performance as my knowledge was gained much like Paul's. I have done a lot of reading on various forums as well as experimentation on my own. I have heard of ways to test performance, perhaps others more familiar with that aspect might present an approach to do such a test.

[evander]

Thanks for your reply, jzwp11, and thanks for noticing this post. I was actually a bit sad when I didn't get any reply since June 12. Hehehe. I can see in your reply that you have a working knowledge about inventory systems. Perhaps I can ask you some questions about it in the future.

All the best.
Joe.

[jzwp11]

Actually, I have never worked with inventory systems, but I have worked with other systems that are similar. If you need additional help, please do not hestitate to ask.

[evander]

Hey jzwp11, so you're online. I saw your replies in other posts. Your knowledge in normalization is amazing. But okay, that belongs to another forum, so I can only laud your skills this far.

Thanks for letting me contact you in case I need help.

Joe.