I'm working on a feature which will pull many (say, 1000-5000) rows from a database, display them to the user and allow the user to edit them. This editing may involve changing text; adding rows at the beginning, middle or end of the dataset; and/or deleting rows. The order of the rows is important (i.e., I can't just shift a newly-added row to the end of the dataset.)
At any point in the editing, the user can hit a save button, at which point the changes are recorded in the database.
Here's where my question comes in: is there an accepted pattern for how to do this type of thing? Thus far, I've just been deleting all the originally-pulled rows and then reinserting the newly-saved rows. However, as each row has an auto-incremented ID, I'm starting to get worried (you can see how a single user could easily blow through 100,000 IDs in no time at all). At some point in the future, I may also have foreign keys which depend on these rows IDs and of course this method will wreak havoc on them.
On the other hand, modifying each individual changed row–including keeping track of deletions and additions–seems like a recipe for mistakes.
Best Answer
You can track what rows the user has modified using the id for that row. Then, update the row that was changed. That should be a simple enough first step optimization and can be implemented in many different ways.
I am concerned about the size of the data that you are sending to the user though. Is that really necessary? Normally users are limited to seeing much less than 1000 rows at a time.