Hacker News Reader

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).

View on HN

This 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.

Also there is the point-in-time architecture https://www.red-gate.com/simple-talk/databases/sql-server/da...

Reply on news.ycombinator.com