-1

I have several tables and many of them has a "status", "measure_unit" and the like columns in them.

There're 2 approaches to work with columns such as "status".

  1. Create separate tables "statuses", "measure_units" in database and reference them from the main tables via a foreign key. Either by string/slug or by id. That is, these "statuses", "measure_units" tables are ones with at most 10 records in them and they will be rarely modified.

  2. Create enums/structs "statuses", "measure_units" ON THE SIDE of a CLIENT app instead of tables in the database. The client would have to ensure integrity.

They both have their up- and downsides.

Which is more and better in different ways approach?

The thins, no matter how you slice it, one will to have to hard-code the names of the "statuses" in queries, for instance:

select * from articles where status = 'active'

that's makes the difference between #1 and #2 even smaller.

2

I had the pleasure of maintaining a one-man application that used enums for everything, and I can say as a young DBA it was a nightmare. I had to contact the app developer a dozen times just to get the values for his enums to build my own lookup tables. The best part was when the customer wanted an additional option for one of them, and he just inserted the new value into the list (what could it hurt?). The result was that ALL of the 7s in the status table used to mean that the document was under review by supervisors, but now they were all marked as cancelled. This did wonders for the data validation guy.

My point is that for me and my work, all values in the database should be in the database. Use enums if you want, but store everything in a lookup table and have relational constraints. I don't trust anything to maintain data integrity other than my database system, whether it's Oracle, SQL Server, MySQL, whatever.

  • have you noticied on the client? – roman-voran Dec 30 '19 at 14:10
1

I've always preferred portability over "tricks". While it's true that nowadays most RDBMS support enums, it has not always been the case. As for the cost of joining with these "status" tables, often the RDBMS optimizer is intelligent enough to keep them in memory as they are "always" accessed so performance is not an issue here.

  • yes, but in either case I'll end up writing and hard-coding statuses in queries. That is select * from articles where status='active'; – roman-voran Dec 30 '19 at 6:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.