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_items
item_id
item_name
item_price

the orders table
tbl_orders
order_id
order_status

the order items table

tbl_order_items
order_item_id
order_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?

You should not delete records from the Items table, since you will lose historical records of them.
Instead, add a Deleted bit flag, or a Status column, to indicate which items are no longer available.

thanks, that's what i will do.