Avoid doing updates as delete/insert

Recently, for a project, we had interactions with a database schema in the following form.

User has multiple Positions, which has multiple Roles.

Now, here, we tried to do the quickest way out, thinking that, when a single entry in Role has changed, or been added, we simply did the following:

1. Delete all Positions of User
2. Delete all Roles of User (Role had a column entry of User ID as well)
3. Reinsert all Positions of User
4. Reinsert all Roles of User.

Why did we do that? Well, quite simply, because we cannot tell from the each object entry if it is an update or insert, and no way to tell deletion since the consumer of such an object actually remove the role from the position array to indicate a removal.

An alternative that sprang to mind was to do a select again before the updates, and do delta compare before updates. That seemed like an awful lot work to do.

So this delete/insert approach works.. but it proved to be a wrong approach.

It creates excessive and unnecessary strain on the database, in terms of redo logs. Depending on how you configure the database, the redo logs might be considerable, especially if you get hit with a 'power user' of say, 300 positions with 200 roles each. Sure, unreasonable example, but if some logic went wrong somewhere...

And also, such insert/remove bumps our primary key id generated value up significantly fast. I have no idea what happens when it reach the maximum limit, if there is one.

And finally, because the primary key id changes so very often, another way has to be introduced to uniquely identify each entry. It could be another running sequence number, or a composite key of a few values. You cannot rely on the primary key id, which becomes somewhat redundant.

A compromise could have been reached.

It might have been better to mark an entry with 'tags'. Eg, a role without an id indicate an insert statement. A role with an id and a 'isUpdate' to true is an update. While a role with an id and a 'isDelete' to true indicate a deletion. Rather than work with it transparently as removing objects from an array, use flags to indicate the operation to perform on each entry. This is more work on the consumer of the object, but better than the alternatives.