Database design question
-
I'm not designing a purchase order system per se, but it is the closest thing to what I'm actually doing so that's why my question/concern involve it. In a "minimal" PO system, you have the following tables: Customers Orders Order Details Products When an order is first created (e.g., #123), product descriptions and prices are pulled from the Products table. That order is marked as paid. A few months go by and some of the products in the Products table get updated with new prices. Now what happens when I look at order #123 and its details? The price of that order will no longer match the sum of its individual products. Is that common? Am I looking at this from the wrong angle? Thanks. - DC
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
-
I'm not designing a purchase order system per se, but it is the closest thing to what I'm actually doing so that's why my question/concern involve it. In a "minimal" PO system, you have the following tables: Customers Orders Order Details Products When an order is first created (e.g., #123), product descriptions and prices are pulled from the Products table. That order is marked as paid. A few months go by and some of the products in the Products table get updated with new prices. Now what happens when I look at order #123 and its details? The price of that order will no longer match the sum of its individual products. Is that common? Am I looking at this from the wrong angle? Thanks. - DC
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
Instead of having one price record per item, have many records with history so you can see what the price was when the order was placed. Something like: Item: ItemId,Name,Description,Supplier,etc.
1,Widget,A standard widget,Widget Co,...ItemPrice: PriceId,ItemId,FromDate,ToDate,Price1,1,2012-01-01,2012-07-01,1.00 2,1,2012-07-01,null,1.50When a new price is added, theFromDateof the new record and theToDateof the previous price are set as Now. You find the current price byWHERE ToDate IS NULLTo find a price for an order you can useWHERE orderdate BETWEEN FromDate and ISNULL(ToDate,Now)You may add NextPrice and PreviousPrice fields if you need to do a lot of lookups. (Specific syntax depends on the particular database in use.) -
I'm not designing a purchase order system per se, but it is the closest thing to what I'm actually doing so that's why my question/concern involve it. In a "minimal" PO system, you have the following tables: Customers Orders Order Details Products When an order is first created (e.g., #123), product descriptions and prices are pulled from the Products table. That order is marked as paid. A few months go by and some of the products in the Products table get updated with new prices. Now what happens when I look at order #123 and its details? The price of that order will no longer match the sum of its individual products. Is that common? Am I looking at this from the wrong angle? Thanks. - DC
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
-
I'm not designing a purchase order system per se, but it is the closest thing to what I'm actually doing so that's why my question/concern involve it. In a "minimal" PO system, you have the following tables: Customers Orders Order Details Products When an order is first created (e.g., #123), product descriptions and prices are pulled from the Products table. That order is marked as paid. A few months go by and some of the products in the Products table get updated with new prices. Now what happens when I look at order #123 and its details? The price of that order will no longer match the sum of its individual products. Is that common? Am I looking at this from the wrong angle? Thanks. - DC
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous
jschell has the right of it, put all the details required to calc the value on the detail record. This may also include any discount/adjustment values. Your invoice report should only pull the descriptors from the product and customer tables
Never underestimate the power of human stupidity RAH
-
Another alternative is to have the details table also record the price. That is basically a requirement if one allows a manager to do a price override.
Good point. Yet shouldn't there be some sort of an adjustment record that can store that rather than leave an auditor wondering why the price was different? Still, you need both the price history and a way to override the price (with a paper trail).
-
Good point. Yet shouldn't there be some sort of an adjustment record that can store that rather than leave an auditor wondering why the price was different? Still, you need both the price history and a way to override the price (with a paper trail).