4 ms·
Audit tables are a good first step, but I am wondering if there was already work being done toward making foreign keys "version aware". Say I linked an order to
by Sytten 5y ago
Audit tables are a good first step, but I am wondering if there was already work being done toward making foreign keys "version aware". Say I linked an order to a discount, it would be nice to be able to know how that linked discount looked like at the time of linking vs what it looks like now in case a merchant decided to change it (but still be able to do a group by id for the given discount).
- jdreaver 5y agoThis is solvable with vanilla table design. Add a version column to the discounts table, make your foreign key point to (discount_id, version) instead of just discount_id, keep old discounts around. Don't mutate discounts, just add a new row with a new version. You can still group by discount_id and exclude the latest version for each discount if you want.
- yCombLinks 5y agoAlso there is the point-in-time architecture https://www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/database-design-a-point-in-time-architecture/ https://www.red-gate.com/simple-talk/databases/sql-server/da...