Hacker News Reader

Comment by bmh100 | Hacker News Reader

That "deleted" flag is extremely useful in data warehousing and OLAP applications. I wish every table had a "deleted" column and an "updated" column.

View on HN

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.

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

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

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

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

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

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

I'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.

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

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

Core 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/

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

Use Datomic, unfortunately.

This problem is pervasive, and it's not easy to solve.

An on update trigger, would be my first thought...

Try 6NF[0], not only do you get separate logging for when the row was/will be marked deleted, but you have a record of when the DB believed that the record was/will be deleted. Also true history and rewind capability.

[0] https://en.wikipedia.org/wiki/Sixth_normal_form

Right, thank you for reminding me about the term. I like to see databases that generally follow this strategy, while providing higher-speed access to current data through materialized views [1].

[1]: https://en.wikipedia.org/wiki/Materialized_view

Reply on news.ycombinator.com