[clavalle]

If data needs to agree PKs and FKs are a way to go. I avoid UNIQUES unless absolutely necessary since they are not structural.

Reference tables for things that don't change or change very seldom are a valuable tool if data across other tables must agree. Check constraints otherwise.

But that's just the bones -- the last line of defense. Having a framework that can push such constraints through the application is very helpful so you are not waiting for SQL errors to enforce integrity.

It is easy to go overboard, though, and it is worth thinking about how important data integrity and agreement actually is before writing it in stone, so to speak.

[paulddraper]

UNIQUES are definitely "structural".

In fact, your the foreign side of the FK needs to be covered by a unique constraint (including PK, which is simply a UNIQUE with slight differences).

[mistrial9]

can you say more about "reference tables" ?

[clavalle]

Sure.

I've also heard them called 'Verification Tables'. They are basically check constraints but stored in a table for reference. If you are using a check constraint in more than one place and it goes beyond a simple 'Y', 'N' it's probably worth thinking about using a reference table instead.

These tables will generally not be changed very often and can be considered 'read only' for the most part. At their most pure, an application can count on certain values to exist in a reference table and might even verify this at startup or populate them with application properties. They could be application level properties if not for the referential integrity needs.

It is often useful, however, to have some reference tables that are only 'mostly read only'. Changeable but largely static.

As a trivial example, a table like state_abbreviations. It is a finite list of data that is unlikely to change.

Data that comes from the government is a pretty good candidate for this kind of thing. It changes, but not often, and you don't want to give users the option to put whatever they want. e.g. Federal School Codes.

Other good candidates are structural aspects of your application. Say, a list of modules that users can navigate to to perform a certain job function. Perhaps you want to give them the ability to compose security roles around those and then apply those roles to users. Also, say, you want to use the same names for menus or reference them elsewhere in a consistent way, a reference table might be the way to go.

[floriol]

I think he means the following, illustrated by example: instead of storing group_name in the users table for each user, you only reference a pk: group_id, and have a separate table groups with the actual name. So the actual value "admin" will only be there once, defending against typos etc.

So basically a normalised database