3

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.

migrated from superuser.com Mar 12 '15 at 17:46

This question came from our site for computer enthusiasts and power users.

  • Displaying 5000 rows to the user itself sounds quite unwieldy. Are you sure there isn't a more fundamental way to modify your workflow? – curious_cat Mar 12 '15 at 17:49
  • I believe it is necessary. The application is spreadsheet-esque so at times the user does need to see many rows. 5000 would be an outlier, for sure, but not necessarily impossible. – Swiftheart Mar 12 '15 at 18:04
  • Is it single user or multiple users active on the same sheet? – Luc Franken Mar 15 '15 at 10:21
  • 1
    Boy, I sure hope there's only one user editing these rows. – Ross Patterson Mar 16 '15 at 1:44
  • 1
    Good database design eschews auto-incrementing row ids, or at least refuses to use them for anything. Foreign keys should be based on values in the rows' columns, not on some independent identity value. – Ross Patterson Mar 16 '15 at 1:52
3

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.

  • The editing-in-place isn't necessarily where the complexity comes from, I feel. It's more how to handle the inserted rows. – Swiftheart Mar 12 '15 at 22:28
  • @Swiftheart surely if there is no 'id' entry for an added row, then it becomes an insert otherwise update the row for that ID. You'll thank us for doing it this way when someone wants edited rows to automatically persist when they click off the grid. – gbjbaanb Apr 14 '15 at 12:32
1

Mass-removal and mass-insertion of records is an operation done at what is known as "machine time", (as fast as the machine can do it,) and could, perhaps, under some stretch of the imagination, consume the entire 32-bit integer number space of automatically generated keys.

But far more reasonable than mass-removal and mass-insertion is an update-only-what-has-been-modified strategy, (as Paul Nikonowicz suggested,) which would mean that insertion and removal would happen only individually, at human time, (as fast as a human can do it,) which would in turn mean that it would take a user many centuries of non-stop clicking and typing to cause enough insertions to use up the available 32-bit integer number space.

Then again, of course, you can always upgrade to 64-bit keys, in which case you have no issue no matter what you do. Or even 128-bit guids, if you like overkill.

Modifying each individually changed row, including keeping track of deletions and additions, is not a recipe for mistakes, it is the actual reasonable, sensible thing to do.

1

A technique I've used to modify and reorder an arbitrary number of records is to merge/upsert the rows, retaining the auto-incremented IDs and using a separate column to manage sorting. The sort order is tracked by the front-end application and passed as an additional query parameter when saving the records, allowing new records to be placed in any position by the application. For MySQL, use the INSERT INTO... ON DUPLICATE KEY UPDATE syntax.

1

Some options I would consider (not exclusive choices):

  • use code that actually does an UPDATE when the record already exists. The user is updating the record so an UPDATE statement would match that better than manually deleting and re-insert which is fraught with danger and error-prone.
  • use a temp table (with the same table structure) to store the transactions that are being presented to the user, plus any changes they make while editing. When they finally save, perform all the validations on the records in this table and then apply them to the base table.
  • using an audit table to store deleted records before deleting them.
  • mark records as pending delete now and then have a background job that runs at an appropriate interval (could be hourly, could be monthly, depends on the need) to actually delete (or archive) them.

Make sure, if your database allows it, to create and enforce foreign key constraints, so you don't leave 'dangling' records as you mention. Keeping a relational database in good shape when you delete records is just one of the practices you need to learn and apply when using databases.

Also, particularly in the case of delete and re-insert, if you use a large enough GUID, millions of records a second would not be an issue. Getting unique id's shouldn't be an issue that drives your architectural design.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.