3 ms·
the problem seems only half solved - the author does not show how they select (only) the most recent record. as soon as the app needs to generate a list of f.e
by alternize 11y ago
the problem seems only half solved - the author does not show how they select (only) the most recent record.
as soon as the app needs to generate a list of f.e. active users, this can get nasty pretty quick. sure, one could use an aggregate like max(version), but once there are joins or lots of records involved, this could become a performance hit...
EDIT: the records are kept in a separate table, so my arguments are moot.
- aidos 11y agoI guess you could work around that by having null in the version column for the active/latest row. Then your queries become 'where version is null' if you want to skip superseded rows. You could then use partial indexes (where version is null) to handle unique constraints etc. EDIT I hadn't finished reading the article, so I didn't see that they were using triggers and the history table. Using the approach I outlined you could keep everything in the same table (if you were that way inclined).
- alternize 11y agowe tried keeping everything in the same table in some web app of ours. we used a version id and an extra versioning status field that kept track of the states "latest published", "latest draft", "archived" which kinda worked fine... the disadvantage is bloat, as in our case the history data was rarely used at all and just there as a safeguard.
- dwrowe 11y agoWhy? They're modifying the main table, which isn't growing with each update. The changes are simply tracked in the history table.