Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    is it correct to delete a record if it's related to others

    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?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2011
    Posts
    2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •