[SOLVED] Advantage of UPDATE vs. DELETE/INSERT?
Posted: Thu Feb 23, 2006 7:10 pm
Hello,
In reading through the MySQL manual, I couldn't find a definite answer to this question, so I come to you.
I'm writing a directory program for internal use in a university department that keeps track of contact information, scheduling and experience, etc. for the teachers of the department. The department is small enough (around 50 teachers) that database size is not likely to be a concern for performance in this application.
The database has a "main" table that holds the contact info, and several "sub" tables to take care of scheduling. When I update these tables, I have a couple of options:
Option 1: DELETE/INSERT
This one is simpler to program. I just delete all rows that contain the teacherID (the reference to an entry in the main table), and insert new ones in their place. However, when I use this method, something twitches in the back of my head that tells me it's probably a Bad Thing to do. For one thing, the auto_increment keys will get larger and larger over time. Do they ever overflow, or are they smart enough to reset themselves?
Once again, it's not likely that the database will ever grow to the point where speed is an issue.
Option 2: UPDATE
This option requires me to SELECT the unique keys from every row containing a given teacherID, compare that list with the list from the form input to see if any are missing, DELETE those that are missing, INSERT those that are new, and UPDATE those that will stick around but whose values may have changed.
I don't have enough experience working with databases to know which method is better. From my lazy-sod-programmer point of view, Option 1 looks mighty nice, but it seems likely that Option 2 is a more Proper(tm) way to do things.
What do you experienced types think?
(I'm using MySLQ and PHP)
In reading through the MySQL manual, I couldn't find a definite answer to this question, so I come to you.
I'm writing a directory program for internal use in a university department that keeps track of contact information, scheduling and experience, etc. for the teachers of the department. The department is small enough (around 50 teachers) that database size is not likely to be a concern for performance in this application.
The database has a "main" table that holds the contact info, and several "sub" tables to take care of scheduling. When I update these tables, I have a couple of options:
Option 1: DELETE/INSERT
This one is simpler to program. I just delete all rows that contain the teacherID (the reference to an entry in the main table), and insert new ones in their place. However, when I use this method, something twitches in the back of my head that tells me it's probably a Bad Thing to do. For one thing, the auto_increment keys will get larger and larger over time. Do they ever overflow, or are they smart enough to reset themselves?
Once again, it's not likely that the database will ever grow to the point where speed is an issue.
Option 2: UPDATE
This option requires me to SELECT the unique keys from every row containing a given teacherID, compare that list with the list from the form input to see if any are missing, DELETE those that are missing, INSERT those that are new, and UPDATE those that will stick around but whose values may have changed.
I don't have enough experience working with databases to know which method is better. From my lazy-sod-programmer point of view, Option 1 looks mighty nice, but it seems likely that Option 2 is a more Proper(tm) way to do things.
What do you experienced types think?
(I'm using MySLQ and PHP)