- Local time
- Today, 17:01
- Joined
- Feb 28, 2001
- Messages
- 31,044
There are two "purist" ways to look at this.
1. Store all prices in a separate table, foreign key = part ID, that is time-tagged twice. Once with the first date on which that date is valid, once with the last date on which that date is valid. Make your JOIN of price to part ID include a test of the date from your price history table.
2. As Rich points out, if you store the unit price in the line-item, there is no violation of normalization because the line-item either contains the date or is the child of an entry that contains the date. So you would have the unit price stored in a table whose keys (directly or indirectly) are date and part ID. STRICTLY SPEAKING, the date should be part of the line-item just 'cause there is a normalization rule dealing with this situation - but in practical terms the date is unequivocal 'cause the purchase order date IS stored in the parent record.
The normalization rule in question says something to the effect that you can't/shouldn't store something in a table if it doesn't depend on all of the keys of the table. Technically, unit price on a given date doesn't depend on the sales invoice number, so this is a nit-picking violation.
Now, here is a REAL wrinkle, but it makes sense in this discussion. If you have the ability to charge a discount for any reason (bulk purchase, preferred customer, special promotion, inventory clearance, you name it), then the unit-price really DOES belong in the line-item 'cause the date is no longer the only key that determines the unit price. I.e. your price table would have a bunch of extra codes (and records) in it to cover the various discounts. BUT the long-term easy way out is to just store the applicable price for that one sale of that one line-item in the line-item table. Compute the effective unit price from some other method, then STORE it. Never again do you have to remember what you charged on that day - or why! (You could, of course, include a field that showed a code for discounts... but you technically don't need that if your business model doesn't need it.)
So in other words, it's a good idea to hold the applicable unit price in the line item entry. But as you know, I go into professor mode sometimes. Since you have expressed confusion on this topic in the past, I wanted you to understand more about WHY it was a good idea.
1. Store all prices in a separate table, foreign key = part ID, that is time-tagged twice. Once with the first date on which that date is valid, once with the last date on which that date is valid. Make your JOIN of price to part ID include a test of the date from your price history table.
2. As Rich points out, if you store the unit price in the line-item, there is no violation of normalization because the line-item either contains the date or is the child of an entry that contains the date. So you would have the unit price stored in a table whose keys (directly or indirectly) are date and part ID. STRICTLY SPEAKING, the date should be part of the line-item just 'cause there is a normalization rule dealing with this situation - but in practical terms the date is unequivocal 'cause the purchase order date IS stored in the parent record.
The normalization rule in question says something to the effect that you can't/shouldn't store something in a table if it doesn't depend on all of the keys of the table. Technically, unit price on a given date doesn't depend on the sales invoice number, so this is a nit-picking violation.
Now, here is a REAL wrinkle, but it makes sense in this discussion. If you have the ability to charge a discount for any reason (bulk purchase, preferred customer, special promotion, inventory clearance, you name it), then the unit-price really DOES belong in the line-item 'cause the date is no longer the only key that determines the unit price. I.e. your price table would have a bunch of extra codes (and records) in it to cover the various discounts. BUT the long-term easy way out is to just store the applicable price for that one sale of that one line-item in the line-item table. Compute the effective unit price from some other method, then STORE it. Never again do you have to remember what you charged on that day - or why! (You could, of course, include a field that showed a code for discounts... but you technically don't need that if your business model doesn't need it.)
So in other words, it's a good idea to hold the applicable unit price in the line item entry. But as you know, I go into professor mode sometimes. Since you have expressed confusion on this topic in the past, I wanted you to understand more about WHY it was a good idea.
