Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AL-Kateb
Starting Member
3 Posts |
 Posted - 2011-03-06 : 04:59:56
|
| Hello everybody, this is my first post in this forum and i hope i can find the answers I'm looking for.i was arguing with my colleague about it and i got a feeling my opinion is wrong but i thought why ask in the forums to see what other ppl think of this.the case is i have the following tables (extra fields were deleted cos they are irrelevant):the items table:tbl_itemsitem_iditem_nameitem_pricethe orders tabletbl_ordersorder_idorder_statusthe order items tabletbl_order_itemsorder_item_idorder_item_order_id (foreign key references to tbl_orders.order_id)order_item_item_id (foreign key references to tbl_items.item_id)As you see from the structure above the rows in tbl_order_items has a relation with the rows in tbl_items, so it would be logical whenever we delete a row in tbl_items all rows related to it in tbl_order_items should be deleted.The problem is! This is orders table which must not be modified! all orders should be kept so clients can refer to them later. But wait!Does this mean i cannot delete anything from tbl_items anymore?Each item has lots of other records related to it (comments, specifications, attachments and other things as well) so if we will always keep all records the database there will be too much data that will take up space and all that might just be deprecated items that are not sold anymore.i suggested that whenever a user checks out rows in tbl_order_items should "break up" with rows in tbl_items! And name, price will be copied to the tbl_order_items for reference.My colleague was saying that's wrong, and records in tbl_items must not be deleted but instead (deactivated) because later a customer might want the specification of the item they have once purchased and if it's deleted it will be all gone and that should not happen.But when i think of it! it does not sound right to waste too much resources just to leave something for reference.So what is the standard procedure for this kind of situation? |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-03-06 : 07:38:49
|
| Can one item be referenced by multiple orders? Does tbl_items contains product definitions?MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
 |
|
|
AL-Kateb
Starting Member
3 Posts |
Posted - 2011-03-06 : 08:07:03
|
quote:
Can one item be referenced by multiple orders? Does tbl_items contains product definitions?
yes it can, since multiple users can order the same product in different orders.and about the other question, yes tbl_item contains the product definition but there is another table as well which has more specified details, tbl_items has the name, description .. etc |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-03-06 : 09:04:12
|
| Well, it does not make sense to keep order item row if you can not see the item name, price etc. Which means there is no sense to delete an item that was already ordered. So your colleague is right. Typically, there are much less rows in reference tables (like tbl_item) then in transaction tables (like order items), so you do not want to loose info for insignificant save in space.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
AL-Kateb
Starting Member
3 Posts |
Posted - 2011-03-06 : 11:45:59
|
| Thanks for your answer, but in fact the table tbl_items has more fields that the ones i wrote and it is connected to another table which has specifications, and another for attachments, those attachments can take up too much space (datasheets, pictures .. etc).so leaving the row in the database can indeed take up much space, so i was thinking we can add the name and the price to the tbl_order_items table as reference.i don't have problem with any of the options technically i just wanted to know what is the standard.so if the standard is to keep the record then so be it : )thanks for your answer |
|
|
|
|
|
|
|
|
is it correct to delete a record if it's related t