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.