Toward Integrity Part 3

Question

Toward Integrity Part 3

cs_troyk

SSCertifiable

Points: 5383
More actions
December 17, 2007 at 12:50 am

#181373

Comments posted to this topic are about the item Toward Integrity Part 3

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dennis Q Miller Old Hand Points: 366 More actions · Answer 1

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.

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.

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.

cs_troyk SSCertifiable Points: 5383 More actions · Answer 2

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

SQL-DBA Hall of Fame Points: 3051 More actions · Answer 3

Dennis Miller (12/24/2007)
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.

The flaw is in thinking that this business rule should be handled at the database layer. The data integrity is ironclad without introducing the new table. The application layer should enforce the business rule. Pass species to a stored proc and get your list of vets, or work with the view!

cs_troyk SSCertifiable Points: 5383 More actions · Answer 4

SAM (9/25/2008)
Dennis Miller (12/24/2007)
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.
The flaw is in thinking that this business rule should be handled at the database layer. The data integrity is ironclad without introducing the new table. The application layer should enforce the business rule. Pass species to a stored proc and get your list of vets, or work with the view!

Why introduce a procedural solution to a problem that can be handled declaratively? Procedural solutions are more complex, more difficult to decypher and maintain, are often less performant, and are more likely to contain bugs.

Furthermore, any business rule which constrains "persisted data" that is enforced outside the database exclusively should be considered suspect. There are too many ways around them at that point -- most notably when Developer B implements some CRUD operations in a new application or app module without the knowledge of the business rule buried in Developer A's implementation.

TroyK