Hacker News Reader

Comment by danielrhodes | Hacker News Reader

Being unable to effectively use foreign key relationships is definitely a downside of using soft deletes. But it's also worth asking if these types of behaviors, which would also include a feature like triggers, really belongs in a database or whether it's better to have at the application level (or at least at a layer above the data layer). I'd argue that ultimately you probably don't want these things at the DB level because you get into a situation where you're sharing business logic between two (or more places).

View on HN

My perspective is DB level triggers are the absolute very best place to put cascading update/delete logic so it only ever needs to be written once and is consistent regardless of any future frontend clients that might be written in a different language and/or framework than the original codebase.

Right now in $dayjob I am converting an old non-DRY codebase from NoSQL data layer format to proper relational SQL backend.

This old front-end was created by verbosely coding up a relational cascading update/delete system for the NoSQL backend, in numerous places redundantly with subtle differences and inconsistencies, making the code brittle.

My current estimate is some front end functions will be reduced in LOC size by 95% once we use the power of SQL in the backend.

And the backend SQL Triggers+StoredProcedure required to replace these long NoSQL frontend functions doing cascading updates/deletes is only around 10% the size of the replaced front-end code.

And now future new frontends can reuse this without exploding the size of their codebase where complex data operations are required. And no need to reinvent the same data handling algorithm all over again (and risk subtle variation creeping in from the different front-end implementation of data algorithms)

I'm less likely to use triggers, but I'll say I pretty much always want proper foreign key relationships set up in the database. Unique and other constraints too. In principal I might agree with you that it's an application level concern, but being able to setup these kind of invariants and trust that they will be enforced even in the face of bugs in my code (and there will be bugs in my code) is just too powerful to let go of in the name of purity. I'd much rather let a bit of business logic creep between multiple layers that discover that I have a whole bunch of orphaned records and no sensible way to reconcile them.

The DB's responsible for maintaining the integrity of data in it, unless there's some very good reason you can't let it do that. It's faster and better at it than your application, 99% of the time, and it can keep doing that even when/if a second application starts using the same database, or when being used from a SQL prompt, or whatever.

Presumably you have a schema defining the tables, the columns, and the types at the least, along with things like unique indexes. So you already have data constraints in your database design. And that's where they belong, to ensure the data integrity, since the database's concern is the data.

If you're doing everything as one big table of entity-attribute-value with generic blobs for the values, then yes you'll have to re-implement all the normal constraints (that the database would handle) in your application and do all your data integrity handling there. And you'll also have to duplicate that logic across every application that accesses that database now and in the future.

Data usually lives longer and has more uses than just one program. So I think it's generally better to put integrity constraints in the database, rather than having to re-implement and duplicate that logic several places.

Reply on news.ycombinator.com