Modelling objects and changes to object which evolve over time in a SQL database

Question

I'm, wondering if there is

at least a name
and/or a generic solution

for the following problem:

I have an object that has some properties.

{
   "name": "Foo",
   "colour": "red",
   "size": 17,
}

The properties of my object change/evolve over time. I'm only receiving differential changes that model the objects evolution via an API (or at least I'm able to transform any update to the object into some kind of a patch).

{
   "start": "2025-01-01",
   "patch": {
       "path": "/name"
       "op": "replace",
       "value": "Bar"
   }
}

I'm receiving the updates to my object in arbitrary order. This means, the updates are not sorted. AFTER I received a patch with a validity starting 2025 there might be an update that has to be applied BEFORE (e.g. in 2023) and changes the past history of my object (as we're in 2024 now).

[
    {
       "start": "2025-01-01",
       "patch": {
           "path": "/name"
           "op": "replace",
           "value": "Bar"
       }
    },
    {
       "start": "2023-01-01",
       "patch": {
           "path": "/size"
           "op": "replace",
           "value": 15
       }
    }
]

So if I'm reading the objects state from my application in 2024, it should have name Foo but colour and size 15. But if I'm querying with keydate >=2025 the name should be Bar.

I want to model this problem in a (Postgre)SQL database.

I want to query my data with queries like:

what is the state of an object with ID xyz at a given keydate?
which of my object fulfill a specific WHERE clause at a specific point in time (e.g. size>10 at keydate 2024)?

The options, that come to my mind are either:

A. store the state of every object at every point in time
B. store one state of the object (e.g. at +/- infinity) AND the differential patches to each object with their respective validity.

Both options have advantages and drawbacks.

If I store ever state of the object ever (A)

➕ I can easily query every them at any point in time

➖ I need to re-calculate every state of the object after a I received a patch with a keydate

If I store a complete state of the object at either beginning or end of time (B)

➖ I can only query the start/end of time properties in the DB and any WHERE has to calculate the state of the object at runtime (in my application, not in the DB!)

➕ I don't need to store data redundantly and update more than one record if a patch is received

So, I'd try to use option A, but it's not trivial. My gut feeling is, that this kind of problem and solution A is so generic, that there has to be a generic kind of software/library to solve it and I wouldn't need to re-invent this wheel.

But what is the name of this problem? Do you know any packages that solve the problem? (Ideally in .NET with EF Core but I'm not restricted to this).

Modelling objects and changes to object which evolve over time in a SQL database

Answers (1)

Related Questions