Help needed regarding keeping orders table history and data integrity
-
I have a small order entry application using SQL Server 2000 MSDE as the database. The database has a Customers, products, orders and order details tables connected with PK and FK relationships. When you have placed an order and the order is completed and you change any details in the products table after the order is completed and you view the completed order it will show the changed product and not the one that was on the order before it changed. That is stupid or am I missing something?
If a order was placed on the product – Duxbury 56k modem and the order is completed and for some reasing someone change the name from Duxbury 56k modem to Molux 56k modem (remember the order details uses the product id to reference the product) if you open your completed order it will show Molux 56k modem and not Duxbury as it was on the order when placed. I hope you understand what I mean. This basically force you to break the data duplicate policy of a rdbms database system.
Is there a way to not allow to change a product detail when it is already in use on a order or quote?
-
Actually it's a bit different with something like an order system. While it's wise to keep the id of the products in the details. You also have to keep all the information such as UnitPrice, description and any other information about the product. You have to do this because an order is an "historic event" and that history must remain static for the whole life of the application.
I'm sure there are other ways of doing this like creating a new product each time you need to modify its content but I never saw a system built that way.
Any comments from other DBAs welcomed!
-
Here's my 2 cents:
All order type systems should have a history table. Make sure you (also) place all orders in the history table (id, descriptions etc.) that way, you will have a "permanent" history of the orders - price, descript, product id etc. Query that table when you need to look up old orders.
-
If it were me, I think i'd add all the product details to the actual order detail table at the time of order creation. I'd add all the relevant product columns to the details, even thought I'd probably display only a handfull of them;
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Good idea... just in case the order system changes over the life of the application and that new information must be reported later on!
-
Orders, simular to invoicing, should and can be set for change right up until shipped, billed or posted.
In our systems, the Order is the historic event and has a status (Open,Pending,Closed, etc). The order contents are attached "Items" which are held in the OrderItems table and also have a status. The items are associated by the order number and may be altered, edited or adjusted up until they are shipped. Once an item ships, it may not be altered, adjusted or deleted since it is also part of the Inventory tracking systems and necessary for the adjustments to current inventory.
-
Rather than going into how our personal system works (which my boss would probably be mad at me for anyway!), I'm going to talk about how this users system could work. I would store anything with the order that you don't want to change, and look up the rest of it each time the order is retrieved. For example, maybe you don't want the description to change on the order, even if it changes in the "administration" portion of your system. You could do 1 of 2 things: You could prevent the user from changing the description with an error message as long as there was a "pending" order (see above post for statuses of Order rows) by adding logic to the SQL that does the administration of the Product. The other option would be to store the ProductDescrpition along with the ProductID in the Order table. On the other hand, if you wanted to be able to update something like the WarehouseLocation of the Product, you wouldn't want to store that in the Order table.
Rick Todd -
I think the problem here, is not in having a history table or in how order details are being tracked, but rather in allowing products to change w/o being a new product.
For example, if I was shipping Widget1 and my supplier upgraded Widget1 to Widget2, Widget2 would not have the same ID as Widget1. In the example by the OP, that is what is happening. Customer ordered Modem A, but someone has gone in and changed Modem A to be Modem B. The RDBMS is working fine, the problem is in the input rules of the system.
I would like to say that the "correct" solution is this...but that probably isn't fair, as there are many ways to do this. So let me just say that the way I would resolve this issue is to not allow the general editing of a product that was already in the system. If a product has been used in an order, it should not be changed. If a product needs a significant change, the original should be tombstoned and a new entry added for the updated product.
Now, there may still be a need for a history table, depending on your setup, but I think the solution that rings more true is to simply not allow that kind of change to your product. This maintains history and data integretity in a normalized fashion w/o adding more tables.
I hope this makes sense...good luck!
-
I agree with John, any edit to the "Product Definition" can not be made if there are Order Items dependant on the Product Key in the Sales History or Orders tables.
-
As the old saw goes, "There's your way; there's my way; then there's the right way."
In this case, yours is the right way. Data integrity should never be jeapordized, and application control of it can break.
Lee...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply