How to do bulk changes in an audited table?

Question

I've got a system where all changes to business objects have to be audited. So an entity MyEntity has a Number property, and when you change this field, the system leaves the original record alone, and makes another record with the new number value, and marks the original record archived. Number is not the primary key. There's also a Version field to track each version of the entity, and an Id field to track object identity across multiple versions. So far so good.

If you delete the entity, the system doesn't delete the record, but just marks it as deleted. So far so good.

Here's the problem. Now the client has a bunch of entities in a list, and there can be gaps:

Item 1
Item 2
Item 3
Item 5
Item 6
...
Item 10

They want to be able to do two new things:

1. Insert an item with number 5, and shift all the subsequent items up a number (5 --> 6, 6--> 7 etc)
2. Collapse gaps in the items, e.g., renumber 5 --> 4, and shift all subsequent items down a number.

This seems really nasty to me, because normally any change to a number would need to be audited, so I can't just change all the numbers in bulk like that. (And it's even more complex still, because each change needs to be approved by a supervisor, and changes can be restored to a previous audit state.)

Even worse, item 4 might exist, but be missing because it's in an archived state. If you want to collapse subsequent items, what should happen to the existing archived item? I can't see a reasonable way to handle these situations while auditing them, and allowing for approvals and restore. Anyone know how to handle this?

Answer 1

I've got a system where all changes to business objects have to be audited.

Try to suggest an exception to this rule for Number property. If this property is also used for other reasons and has to be audit anyway, then you could suggest a new field OrderByNumber and have it free of audit requirement.

If your argument faces resistance, I would try to rename Number or OrderByNumber to something ridiculous but obvious. It will make people smile and they will see the point: NotABusinessConcernNumberButUIElementNumberUsedForVisualOrdering.

If there is no way you could have an exception to audit rules for your business objects, then you would have to create a different layer of UI/presentation objects and keep a mapping between the two.

Answer 2

I never really solved this problem in a fully satisfying way, but we handled this by implementing a special mode, available only to the super admin, where auditing is disabled for this kind of identity changing operation, on the grounds that the Number, while not a primary key, is an essential user key, and if you change it, you're changing the object identity to a new thing, which starts with a clean slate. Not ideal, but a reasonable compromise.