Dennis Miller (12/24/2007)
It's great to see some attention to conceptual/logical modeling from the SQL Server community. In my experience, DB practioners with that persuasion tend to jump right to table design, skipping some formative processes.
A very good observation... the title of this series is meant to have a double meaning, and you've hit upon the motivation for the second.
I was especially interested in the presentation about unification (referred to as "overlapping foreign keys" in the article). Proper unification is a very important--and often overlooked--aspect of designing a logical model that enforces the business rules.
Another more general name for this is a "tuple-in-join" constraint, for those that would like more search engine terms.
I like your example because the unification issue is apparent at the logical modeling stage. More often, it does not crop up until surrogate keys are introduced during physical design.
But, I do have an observation/question (not quite sure which) about which I hope some discussion will ensue.
It has to do with the need for superkeys to the Vets and Pets entities in order to support the foreign keys. In other words, we have a situation where the primary key is not sufficient to support the business rules. Why? Superkeys are introduced to overcome that shortcoming. I understand the work around, but it seems to me to be exactly that. I can't help but think the logical design is flawed in some way that leads us to that point.
I want to think about design alternatives a bit before I comment further, but maybe someone else as something to contribute.
Again, a good observation that this is a workaround. I hold that this is a workaround to the lack of SQL's support for FK declarations to views and/or the lack of T-SQL's support for 'assertions'.
There are at least 2 answers to the "superkey requirement" question (more precisely, "reducible superkey", "RSK" from here on), each implied by the computational layer we're talking about:
1) At the physical level, we have the requirement because SQL Server (along with most, if not all SQL-based dbms implementations) require that a FK reference a set of columns that participate in a uniqueness constraint (be it because of a PK declaration or, as in the article, due to a uniqueness declaration). Why this is, I'm not sure. Obviously, it would be trivial for the engine to allow FK-to-RSK declarations anytime the RSK includes a subset of columns that are declared unique. But SQL forces us to explicitely declare the uniqueness constraint before we can "FK to it".
2) At the logical level, the need to "FK to" the RSK in each of the Vets and Pets tables arises specifically because of the business rule that a pet of a particlar species can only see a vet that is licensed for that particular species. One way to demonstrate the logical necessity of the RSK-based FK is to:
a) remove the 2 FKs as described in the article
b) optionally, remove the SpeciesName column from the VetPatients table because it's no longer needed for participation in the FK
c) declare 2 FKs only to the candidate keys (CK) in each of the Vets and Pets tables, i.e., VetPatients!LicenseNbr values must be a subset of Pets!LicenseNbr values, and VetPatients!TaxID values must be a subset of Vets!TaxID values.
Now, when we (d), attempt to add a row that violates the business rule, the database will accept it. This shows that the FK-to-CK(only) design is inadequate to enforce the rule:
-- Pet 1003 is a cat, but Vet 900700002 is only allowed to treat dogs
INSERT INTO dbo.VetPatients(LicenseNbr, TaxID/*, SpeciesName now superfluous*/)
SELECT 1003, 900700002
To be sure, there are other database designs that will enforce this rule. However, for this article series, I'm designing more for simplicity, which the alternatives lack. In an upcoming series, I will be taking some of the solutions here and proposing alternative designs. I'll then evaluate the different designs against a number of criteria so that the reader can get a sense of the tradeoffs that come into play.
TroyK