How to save many changes to database data?

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.

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.

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.

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.

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.

Filed under: softwareengineering - @ 17:46

Thẻ: database, mysql

Thiết kế website giá rẻ

Danh mục

How to save many changes to database data?