4 ms·

INSERT only, no UPDATEs. Queries return most recent record. Periodically groom older entries.

moralestapia 5y ago
Just wanted to x2 this, in several projects we end up with this setup and gets the work done. If you want to get just a bit more fancy, add a hash that depends on previous entries, like some sort of proto-blockchain.
andrei_says_ 5y ago
Wouldn’t grooming older entries eliminate the trail?
- romeoblade 5y ago
  You generally dump them into a data warehouse after a certain amount of time so they are not in the live database anymore. You still have the records and can still reference them for reports and compliance reasons.
  - specialist 5y ago
    Yes and: Most of our queries (current patients) only needed the most recent records. For our clients, it was ok that older data took a bit longer to retrieve, because those patients weren't present (eg on the phone vs not currently in the emergency room).
  - andrei_says_ 5y ago
    Possibly a tangential question but how would one deal with data erasure requests in such a scenario
beagle3 5y ago
This works well for the audit part - have done this myself more than once - but makes it hard to impossible to get good query performance for some kinds of queries. You need to have a trigger that keeps an "up-to-date" view of only the latest version -- or alternatively, have a trigger that keeps the "insert only" table based on one you treat as usual (delete/insert/update)
- specialist 5y ago
  SQL Server has temporal indices. Our queries seemed fast enough. Sorry, I don't know Postgres, but would expect there's some kind of solution. We stored medical records. Datafeeds were very chatty. Updates would arrive out of order. Resolving "single best record" (aka "source of truth") was trivial via queries. The norm seems to be to adjudicate as data comes in. So my solution really upset a lot of people. To your point, I did have to change the schema for scripts and allergies. To get the indices arranged just right so that GROUP BY queries were performant. Further: UPDATE only strategy enables temporal testing; just specify time range. Saves a lot of time, effort. Versus norm of reloading the data from scratch every test run.

Just wanted to x2 this, in several projects we end up with this setup and gets the work done. If you want to get just a bit more fancy, add a hash that depends on previous entries, like some sort of proto-blockchain.

andrei_says_ 5y ago

Wouldn’t grooming older entries eliminate the trail?

romeoblade 5y ago
You generally dump them into a data warehouse after a certain amount of time so they are not in the live database anymore. You still have the records and can still reference them for reports and compliance reasons.
- specialist 5y ago
  Yes and: Most of our queries (current patients) only needed the most recent records. For our clients, it was ok that older data took a bit longer to retrieve, because those patients weren't present (eg on the phone vs not currently in the emergency room).
- andrei_says_ 5y ago
  Possibly a tangential question but how would one deal with data erasure requests in such a scenario

beagle3 5y ago

This works well for the audit part - have done this myself more than once - but makes it hard to impossible to get good query performance for some kinds of queries. You need to have a trigger that keeps an "up-to-date" view of only the latest version -- or alternatively, have a trigger that keeps the "insert only" table based on one you treat as usual (delete/insert/update)

specialist 5y ago
SQL Server has temporal indices. Our queries seemed fast enough. Sorry, I don't know Postgres, but would expect there's some kind of solution. We stored medical records. Datafeeds were very chatty. Updates would arrive out of order. Resolving "single best record" (aka "source of truth") was trivial via queries. The norm seems to be to adjudicate as data comes in. So my solution really upset a lot of people. To your point, I did have to change the schema for scripts and allergies. To get the indices arranged just right so that GROUP BY queries were performant. Further: UPDATE only strategy enables temporal testing; just specify time range. Saves a lot of time, effort. Versus norm of reloading the data from scratch every test run.