http://weblogs.sqlteam.com/jeffs/archive/2008/08/13.aspx:

Think about the following logical data model:
* You will find multiple Companies
* Each Company has numerous Projects
* Each Project has numerous Tasks
* Each Task includes a Status, selected from the global listing of pre-defined Statuses.

Let's state that we choose that the main key of Companies, Projects, Tasks, and Status are Identity (auto-number) posts, since we wish to auto-generate primary secrets for individuals tables.

So essentially, we've 4 tables:
Status (PK: StatusID)
Companies (PK: CompanyID)
Projects (PK: ProjectID, FK: [Companies].CompanyID)
Tasks (PK: TaskID, FK: [Projects].ProjectID, [Status].StatusID).

Now, let me add one little wrinkle. Guess that the accessible Statuses for every Task isn't defined globally, but at the organization level. That's, each Company features its own listing of Statuses that an activity could be designated.

Which means that the Status table now requires a Foreign Key mention of Companies table (to point recognise the business each Status goes to):

Companies (PK: CompanyID)
Status (PK: StatusID, FK: [Companies].CompanyID)
Projects (PK: ProjectID, FK: [Companies].CompanyID)
Tasks (PK: TaskID, FK: [Projects].ProjectID, [Status].StatusID).

Are there more changes we have to make for this data model? Or perhaps is simply adding a CompanyID column towards the Status table enough to facilitate this transformation? Remember, our goal of course is full referential integrity using primary and foreign key constraints whenever we can.

Well, there's an problem:

Nothing within this data model stops us from setting a standing to some Task that's not defined for your Task's parent company. We've not a way of enforcing this at this time with this current constraints. Our physical data model is problematic.

This is easily fixed, however it only works by breaking the "all tables only need a name primary key" rule.

First, don't forget this: simply because a name column is exclusive does not necessarily mean that that column can't be part of the primary key.

He continues to point how composite secrets may be used to fully enforce and constrain your computer data model with something similar to this:

Companies (PK: CompanyID)
Status (PK: CompanyID, StatusID, FK: [Companies].CompanyID)
Projects (PK: CompanyID, ProjectID, FK: [Companies].CompanyID)
Tasks (PK: TaskID, FK: [Projects].(CompanyID, ProjectID), [Status].(CompanyID, StatusID)).

I've lengthy been keen on fully enforcing/constraining my data models, however, frequently I've found myself in situations like the aforementioned and that i arrived at a mix-streets:

To completely enforce in order to not fully enforce.

The apparent lower-side to this is actually the apparently over-complex design.

Now, I understand there is not always a "correct" design, however for situations such as these.. I am searching for feedback when it comes to guidelines.

Advantages, disadvantages and general ideas about this design or fully enforcing your computer data model design?

**Observe that this may spawn a debate regarding where duties lie when it comes to enforcing your computer data model (database or application or both). For sake of debate, In my opinion that the data model should enforce itself - please answer under this presumption. **

I'd produce a CompanyStatus table the industry many to a lot of between Company and Status and describes which from the statuses are relevant for any given company. Then, tasks get designated a CompanyStatusID as opposed to a StatusID.

This prevents you against getting duplicate statuses inside your Status table - a lot of companies can share exactly the same Closed status for instance, what's best normalization.

So, you don't need to make use of compound secrets to be able to correctly enforce constraints. I favor to make use of single autoincrement primary secrets which have no meaning (surrogate secrets). That's better quality than making a belief that the key is going to be unique (for example SSN), when there's always the possibility which will come out to not be, yet you have to keep data anyway because the application requires it (so a distinctive constraint is of no help here).