Hacker News Reader

Comment by paulryanrogers | Hacker News Reader

DB constraints can be a hindrance if the business rules are in flux, and you already have them in the app layer. That said I did build a relatively mature system using a lot of DB constraints, even using magic prefix in error messages and constraint names to bubble up into the UI.

Nowadays I stick to only FKs, unique, and size parameters.

View on HN

I agree. It's not wise to encode business rules into the database. However, there are certain kinds of constraints that are still valid, specifically if they are data structure constraints (like a value must be >= 0).

This has been a continual discussion between me and my coworkers. Do you know of any reading we can look at to better understand the tradeoffs of when it is appropriate to put constraints on the data?

The article is about the basic constraints you mentioned, FKs, PK/unique, check constraints. I agree with keeping the database as dumb as possible with only those, heck even dropping those (except for PKs) at the beginning gives you more agility.

> DB constraints can be a hindrance if the business rules are in flux, and you already have them in the app layer

What you here mean as "DB Constraints" if not FK and etc?

Check constraints can compare new values against bounds different than the column's type definition. Like maintaining a SMALLINT between 1-100. Or one could even write trigger based checks that ensure integrity among multiple records' values. (Though I don't recommend it unless the constraints are unlikely to change or one has little control over app layer validation.)

Reply on news.ycombinator.com