6 ms·
They do in my schemas :) One extra tip, which I have found useful, is to make the deleted column a time data type (just like created and updated), but nullable
by Todd 11y ago
They do in my schemas :)
One extra tip, which I have found useful, is to make the deleted column a time data type (just like created and updated), but nullable. That way, your Boolean check just needs to change to an IS NULL check, but you get the additional 'when' information without using an extra column.
- pjungwir 11y agoThat is the normal pattern in Rails apps using the `acts_as_paranoid` or `permanent_records` gems (`deleted_at` to match `created_at` and `updated_at`). But I often also have `deleted_by_id` to capture Who, and I wonder if I shouldn't just have a separate `deletions` table with the who/when and other context, and then `deletion_id` on the record. And then I wonder if I should track updates too. There are auditing solutions to record all that, but the ones I know are (rightly) not really designed for building application logic on top of. The idea of a relational schema having some kind of temporal dimension letting you get at changes is something that's been on my mind a lot lately.
- mason55 11y ago> There are auditing solutions to record all that, but the ones I know are (rightly) not really designed for building application logic on top of. Yes, a big problem with table-level audits is that you lose all kinds of information about the other entities in the system. Sure, now you have an audit log of when a row was changed, but you don't really know anything about the state of all the other pieces of the database at that time, so you can't really usefully reconstruct what the entity looked like at the time it was modified. In theory you could parse through the whole audit log to reconstruct the state of the DB but in practice it gets very complicated.
- bmh100 11y agoA better solution is to perform row-level snapshots with a compressed storage format, such as a column store. I maintain a database which takes monthly snapshots of data and supports an application that allows period vs. period comparisons of aggregates or even individual rows. In my case, I use snapshots, but more space efficient (at the cost of computation) would be to only store changed records, then dynamically determine which data to show based on the desired periods and sorted the row changes.
- pjungwir 11y agoI think what mason had in mind, which I agree is a major pain point, is when a central table "owns" records in other tables, e.g. a `book` might have several rows in `pages`. I want to say "give me edition 3" and get not just the book at that point but all its pages too. Tracking changes to the book is not so hard, but reconstructing it with all its child records is a pain.
- mason55 11y agoYes exactly. Piecing together the state of all the foreign tables across the system at a specific point in time is difficult/painful. This is one place where document stores really shine as you generally keep everything in a single place. When you update a document you don't have to worry about the values of all the foreign keys, you just save the current version which contains all your values.
- bmh100 11y agoI have dealt with this scenario before. The type of data is called "slowly changing dimension" or SCD. SCDs might never change, or change daily. It all depends on the business logic. To implement this tracking, I have seen row-level historical snapshots for auditing and dimension-level tables to track the less-important dimension changes. For health data, row-level snapshots with a key generated from the data might be most appropriate. For data that has less legal implications, a table that has "id", "dimension value", and "timestamp" may be enough. From that, using from advanced SQL, you can even produce dimension time intervals. An example where that would be useful is tracking the average time a support ticket spends in "Waiting on Customer" or a call spends waiting in queue to be answered.
- KingMob 11y ago> The idea of a relational schema having some kind of temporal dimension letting you get at changes is something that's been on my mind a lot lately. You may want to check out Datomic. It uses an immutable, time-based model that covers deleted_at and many more scenarios (e.g., it's effortless to ask, "what was the state of this object last month?" without the need for looking at old backups.)
- 15155 11y agoI'd be rocking Datomic everywhere if it were free software. It's really too bad - if they offered paid support and otherwise-sane licensing, I'd be all over it.
- biot 11y agoYou should take a look at event sourcing. Every action is stored as a separate event and periodically snapshotted. To see how something was modified, you can replay all the events forward from day 0 or from any snapshot.
- jtc331 11y agoCore team member Magnus gave an excellent presentation at PGConf this year about how to achieve that with PG triggers and schemas: http://www.pgconf.us/2015/event/60/ http://www.pgconf.us/2015/event/60/
- NickNameNick 11y agoHow do you manage referential integrity? If you have 2 tables, A and B, where B references A Ideally I couldn't set deleted on row in A until all the rows in B that reference it have also been set to deleted. If I wasn't using soft deletes, I'd just use a foreign key from B (a_id) to A (a_id), but with soft-deletion, that constraint doesn't get enforced.