I've been wondering whether constants like "Approved, disapproved, pending" or "Single, married, divorced" should be grouped up in a single table holding references to what module they are used at in the database or into separate tables for each relevant module. Which one is more practical?
+7
A:
Separate tables in order for the database to enforce foreign key constraints that only applies for the referencing table.
Philip Fourie
2010-03-07 14:53:14
...still for the db-based approach, I absolutely agree with you, Philip. +1
mnemosyn
2010-03-07 14:56:42
Any penalties for having separate tables over a single table?
Jonn
2010-03-08 00:35:00
+1
A:
How 'constant' are these constants? I'm currently storing constants in code, since they can, by definition, not change. If it's not user-configured, don't put it in the DB.
Having a zillion foreign key constraints of that type in the DB is useless and will make your performance suffer badly, if that is of any concern.
But I know my opinion on this is rather rarely shared.
mnemosyn
2010-03-07 14:55:54
+1
A:
They should go in separate tables, so you can make use of foreign keys. For example, let's say you're talking about a Users table here, designed as such:
UserId int
Status int
MaritalStatus int
You could define a UserStatuses table
StatusId int
Name nvarchar
with the rows for Approved, Disapproved, and Pending, and then do the same approach for UserMaritalStatuses. This also maps nicely to making the same constants in code for when you're referencing these tables.
Greg Shackles
2010-03-07 14:56:36
+1
A:
You shouldn't be storing constants in a database in my opinion, keep them in the code.
Both examples you gave I would store as ENUM's in the database.
Mark
2010-03-07 14:56:37
I don't like checking a record's status against a string. Is that even right?
Jonn
2010-03-07 23:28:39
most SQL implementation i've used store the enum's internally as an integer thats an index into the allowed enum values, just as C would. SELECT's should be doing comparison's against the index not string comparisons.
Mark
2010-03-08 03:49:15
+2
A:
I agree with mnemosyn - whenever possible I would enforce these things which cannot be modified by users with constraints rather than foreign keys.
However, one wrinkle would be if you need to include user-friendly names in queries for reporting, BI, or ad-hoc users. Then a foreign key table would be pretty handy.
Ray
2010-03-07 15:13:41
I don't necessarily agree - things **will** need to be modified, and it's much easier adding a new row to a lookup table which enforces a FK constraint, rather htan having to drop and recreating a constraint...
marc_s
2010-03-07 16:35:21
easier yes, but how often will it happen? if you are storing a status code (as in the question "Approved, disapproved, pending") and your business process makes this extremely unlikely to change, then I believe the convenience of a lookup table is far outweighed by the simplicity and performance of a constraint.
Ray
2010-03-07 16:44:12
It's about how you expect frequently you expect the "dimension" to evolve and how large you expect it to be. If the scope of the dimension is small (under seven or so) and highly static, like marital status (single, married, divorced, separated, widowed, unknown), then not mapping it to a table is appropriate.
Adam Musch
2010-03-07 22:55:18
The alternative being an enum in code? Then the counterparts in the database would have to be ints corresponding to the enums??
Jonn
2010-03-08 00:44:12
@Jonn - exactly, though I would typically use tinyints. You have to be careful to keep your constraint or lookup table in sync with the enum.
Ray
2010-03-08 12:27:30