Where Constants should be stored in the database

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 databa开发者_高级运维se or into separate tables for each relevant module. Which one is more practical?

Separate tables in order for the database to enforce foreign key constraints that only applies for the referencing table.

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.

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.

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.

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.