Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Oct 2006
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record Update/Edit

    I have a number of tables that are linked together. If a record record is created in the "main" table, atleast one record or more is created in the other tables.

    My users have requested a edit function and I was wondering which of the below methods would be best.

    1. Delete the main record and all record relating to it in all the other tables. Then creating new records for the tables with the new data.

    OR

    2. Check if the the data has changed, and if it has either create a new record if one does not already exist or update if it already exists. Or if data did exist and no longer does delete the record.

    For simplicity sake the first one was the option I would go with but I am not sure if it would create more headaches in the long run.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Assuming all of your primary and foreign keys are "meaningless" fields, meaning they are not fields that the user attaches any meaning to and cannot change, then I'd stick with updating existing rows.

    For example, if you used something like a last name for the primary key, then a user making a change to a last name is a royal headache to update and it's easier to just delete and insert again. You'll still have to code for duplicate key issues.

  • #3
    New Coder
    Join Date
    Oct 2006
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I used auto inc fields for all my tables. All other fields have a chance for duplicate values.

    I will do the update method then. Thanks for the input.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    if you use INNODB tables you can look into using ON CASCADE DELETE and thus deletion from your primary table will also remove those records from your secondary tables.


  •  

    Posting Permissions

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