July 10, 2006 at 12:23 pm
I have and ordering sql server database 2000. It has a order , order details, product, product detail, customers, suppliers tables. How can I keep backups of previous orders and the products on the orders? If i turn off the envorce cascade delete, select, insert, update options on the fk constraint between the product and order table it will keep the oreder when the product that is on that order is deleted but I will only be able to see the order details and a productid no details on that product. If I the cascade select, insert, update, delete option is on - when the product is deleted that is on the order that order will also be deleted. Is there another way of keeping record of previous orders and the products on that order even thou that product is deleted?
July 10, 2006 at 2:21 pm
If you delete the product you will not be able to see any of the profile data since the key relationship will be broken.
It is actually a bad practice to delete a record of this type from the system. Isn't there some sort of status field that you can set to disabled? That way it will still be available when you look up your orders but will not be available for purchase.
I've seen people use naming conventions when their systems do not have a status feature. For instance they may change the product name by prefixing it with something that will make it appear at the bottom of the list like 'zzz'. For example if "Blue Socks" were being disabled they would simply prefix the item name to make it "zzz Blue Socks".
I hope this helps.
July 11, 2006 at 2:24 am
What if I keep the PK , FK relationship between Orders and Products and add a new table OrderHistory wich will keep all the orders placed data. In the end if you delete a product that is on an order, that order will also be deleted but a record is kept of the order placed in the OrderHistory table.
Or I keep the PK, FK relationship but when a pruduct is deleted that is on an order it will give you a message that the product could not be deleted and that the order must first be deleted.
July 11, 2006 at 7:11 am
My opinion:
Do not delete any products at all, or at least not when the product is used in an order.
Generally, it is possible to delete a product that you have erroneously entered (duplicity etc.), if it wasn't used anywhere else. As soon as the product was used in some other table (orders, invoices, etc.), the product should ALWAYS stay in the database. Use a status column in products table (Active/Inactive, or with more options - like Planned, Active, Remainder, Closed).
If you create the OrderHistory table, you'll have the record of the order, but that won't help much, since the product will not exist in the database... so you will know, that the customer ordered 10 pieces of an unknown product. Not really good. I can only repeat : don't delete any products.
July 11, 2006 at 11:19 am
I totaly agree. To keep referention integrity all the tables have PK, FK relations but tables like the orders and prudcts, the enforce cascade delete and update option is off. So all the orders which contain the product that is being deleted must first be removed before the product can be deleted.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply