What's the best way to save/track change requests on multiple objects in a database?

Question

I have a schema containing contracts, sub-contracts, services offered by each of those, and sub-services.

I also have an application that allows me to extend the duration of the contract and to modify data on the services and sub-services.

I've been thinking about the best way to keep track of the extension and I've found these solutions which I'm not happy about:

• creating an object that contains all the fields updated, this isn't very dynamic since services and sub-services can change.
• creating an object connected to clones of contract, sub-contract, services, sub-services... but this would create a lot of records.
• saving all the data in an object's field as a json or as a pdf document, but this doesn't allow me to make queries if I want to make a report later.

Is there a pattern or better way to do this?

PS: The application sends a request to an external service to update those fields which will be updated after being given the OK, so in the meantime, I have to save the fields and values that have to be updated.

Answer 1

It's not clear whether you are satisfied with an eventually consistent model, but using commands to create a changelog might be applicable here. Check CQRS for details.

This would allow you to store changes (as well as keep an audit log to track changes etc.) in a uniform format (like "update", "entity", "property", "newvalue: 2" type of thing). However the commands are not in an easily queriable form, so if you need to access the data while it's still queueing and waiting for update to external service, this might not work. If however the data is not considered valid until the 3rd party services are run, this might be something to look into.

Answer 2

You could either save the requests as they come in (to an event store of some kind), or you could keep multiple versions of the underlying data, such that each change which needs to be tracked creates a new copy of the entity.

In choosing between these two, you are making the same tradeoff one always makes: storage cost vs compute cost.

Let's say I have some entity E(key, attribute 1, attribute 2, ... attribute N). I make a request to change attribute 1 only, and therefore store only the key value, the new value of attribute 1, and a timestamp for the event. This is cheap on storage. But reconstructing the state of the entity at any given time becomes very complicated, since you need to read the initial state, and then aggregate all of the state changes up to the point in time you care about.

On the other hand, I could store a whole new entity with all of its attribute values, including the new value for attribute 1, along with certain metadata representing the interval of time over which this version of the entity is valid. This is clearly more expensive in terms of storage, but determining the state of the entity at any time in the past becomes trivial.

In almost all situations I would go with option 2. Storage is cheap, and read performance is typically the number 1 performance concern. Nobody wants to wait around while their data is retrieved, whereas people tend to be more accepting when saving data takes a fraction of a second longer than it might theoretically need to.

For the record, there's a bit of a fad going on right now for option 1, with a pattern known as "event sourcing". I suppose it has its place, but not for the vast majority of simple applications out in the world.

Oh, PS: never go with the "store it all as one big mutable json file" approach.

Answer 3

Not sure if I totally understand here but I'd say the data model is the data model (superset of fields required) and the services decide which fields on the data model they are going to expose and allow modification of. You don't have to return all fields in a service which shouldn't give access to the extended fields and only expose those extended fields through the extension service.

Answer 4

If you’re using a database for storing your objects this is the preferred option:

Make your entity E(key, attribute 1, attribute 2, ... attribute N, valid_start, valid_end) and manipulate the valid_… columns to show when the respective state was stored or replaced by some other state.

This is preferable, because:

a) you can handle deletion and reinsertion of records easily (like a contract becoming inactive and then being reactivated, or a service deleted from a contract and then being reestablished some time later).

b) the theory of that is well developed: See „Developing Time-Oriented Database Applications in SQL“ by Richard T. Snodgrass, 1999, available from his homepage, which considers primary keys, uniqueness conditions, referential integrity, joining temporal tables and coalescing (selecting fewer columns in a temporal table and adjusting the time fields such, that only single records for each state are reported, ignoring changes in the other columns). It even deals with practicalities of the approach in the SQL databases of that era (providing code samples). See also Temporal Databases.

c) I have seen this in the wild in business critical deployments.

d) I have used it myself in a productive data warehouse.

The only drawback is, that things can become slow, if the rate of change is high.