3 ms·
I have long wanted to use an enum but never have, despite using Postgres for more than a decade. > In comparison, writing a validation on the application si
by combatentropy 8y ago
I have long wanted to use an enum but never have, despite using Postgres for more than a decade.
> In comparison, writing a validation on the application side is easy
But then you went there. I was going to say, instead of enums I always end up just make a table of the choices, because invariably I will want to store one or more columns of information about the choice. Anyway, a foreign key, as opposed to an enum.
Writing validation on the application side is easy up front, but over the years I have been moving more and more smarts to the database:
- it's often less typing in SQL than your procedural language of choice
- some constraints are easy in a database but 1,000 times harder in a procedural middle layer (uniqueness, ACID transactions, etc.)
- it's more efficient, because it saves the transit of data that your application-layer validation often needs from the database to do its own thing anyway
- Sharlin 8y agoYes, it's a relational database so use relations to express things!
- setr 8y agoI think you forgot one of the more important points against application-side validation: any future applications would have to also implement the validation or your data potentially corrupts. Applications that also include possible meatbags hand-modifying values for some "special" scenario (CEO order, emergency situation, etc) The DB is a gigantic bag of data: if its got anything useful in it, then everyone will eventually want to put their grubby little hands all over it
- derefr 8y agoIn my experience, the key tenet for good database architecture is to assume that someone else is going to write a program, without your input, that's going to shove data into your DB's tables, without the involvement of your application layer. If the data that ends up in the database is wrong in such cases, then your application will be left holding the bag when it sees that data. Much better to just tell your DBMS the rules you want enforced, such that when that ne'er-do-well (which might be, say, future you) attempts to insert crazy data, it just doesn't work.
- caf 8y agoIn my experience one of those "associated columns of information about the choice" is frequently an end-date, beyond which the choice is no longer available (so that old records can continue to be associated with old then-valid choices, but new records cannot).