November 3, 2014 at 9:34 am
I have a design question. I would provide a list of table definitions, but my confusion over the design is the reason for my post. I am wondering if what I am about to do breaks normalization rules or is there a better way to do what follows (this is my first post I hope I get it right).
I have a products table that for the sake a brevity contains just a product ID, a Description and Price.
Product
ID
Description
Price
I have an Orders Detail Line Table. This is where the question lies. Do I keep the Price of the Product that was ordered in the Orders Detail Line Table? On one hand that seems to break normalization rules. On the other this prevents a problem if in the future the Price of the Product changes. The Price of an order should always reflect the Price at the time of the Order, not the current Price.
Order Detail Line
Order ID
Line ID
Quantity Ordered
Order Price
Should the Price be removed both tables and put in another table that shows the Price of a Product on a Specific Date such as?
Product Price
ID
Product ID
Price
Date/Time
Copying the price into the Order Detail Line seems cleaner, but also feels like I might be breaking normalization rules.
Thanks so much
Again, I apologize if the format of this post is incorrect. Since I was unsure of the table design I wasn't sure how to write the post.
November 3, 2014 at 9:44 am
If you want to record what the price was at the time of the order, then I would keep the price in both table. This is normal (no pun intended) practice in most systems I have worked with.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 3, 2014 at 9:50 am
How about using an Orders History table and store the ordered price there?
Tom
November 3, 2014 at 9:53 am
Hi, and welcome to SSC.
You should record the price in both places; while it may seem like redundant data they're actually separate attributes - one's the list price and the other's the price paid, and nary the twain shall meet (just ask any Oracle Salesman).
For completeness there should probably be a way to track list price changes over time, either directly in the Product table or via a child table (ProductPrice ?), that would depend on your requirements though.
Cheers
Gaz
November 3, 2014 at 9:58 am
OCTom (11/3/2014)
How about using an Orders History table and store the ordered price there?Tom
I don't see a need for an Order History table. The Orders table can be it's own history table.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 3, 2014 at 11:37 am
I would like to thank you and the others that responded.
November 4, 2014 at 2:10 am
eichnerm (11/3/2014)
Copying the price into the Order Detail Line seems cleaner, but also feels like I might be breaking normalization rules.
You don't appear to be violating any Normal Form here. I guess you have a Product identifier in the Order Detail table but I'm not sure which attribute that is. Even assuming you do have a product attribute in that table, putting the price as well wouldn't create any non-key dependency. The point here is that the price changes and therefore product does not determine price, so there's nothing necessarily wrong with putting the price into the order details table.
November 6, 2014 at 2:41 am
The whole point of holding prices in two places is to avoid updating one when the other changes - the two prices are two different things, and trying to cram them into one would be guaranted to create update anomalies: since the whole point of normalisation is to ensure that the schema prevents such anomalies you are clearly more normalised with the twoprices in the two different appropriate places than with just one price trying to be two different things at once.
Tom
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply