Toward Integrity

Modeling Constraints in the Database

Article 3: First look at the Pets - Vets relationship

Note: This is part 3 of a multi-part series. You can find the other articles here:Part 1 and Part 2.

This is the third article of a series in which we explore a variety of business rules. We'll take a look at their expression in a conceptual model, and then drill into the details of how they can be modeled at the logical level, and ultimately implemented using T-SQL. In the first article, I introduced the overall conceptual model and explained some of the business rules that apply. In this article, we focus on the "Vet" and "Pet" entities and their relationship to each other.

Note: I remind the reader here that the our bias for the logical model is toward correctness and simplicity rather than, e.g., flexibility . Also, the physical implementation will reflect the logical model more closely than might be prudent in practice.

For reference, figure 1 shows the complete conceptual model diagram for our Pets and Vets solution:

Figure 1: Pets and Vets conceptual model

Figure 2 shows the entities of interest for this article:

Figure 2: Conceptual sub-model, Vet and Pet

The business rules we will deal with today are:

• A pet may be the patient of zero, one, or more vets / A vet may provide healthcare for zero, one, or more pets.
• A pet may only see a vet if that vet is licensed to treat that species of pet.

The first thing to notice as we translate the conceptual model to the logical model is the introduction of the associating table "VetPatients" (figure 3). This allows us to resolve the many-to-many relationship between Vets and Pets. Experienced data modelers should recognize this pattern immediately. In practice, it is not unusual for additional attributes (besides the key attributes from the associated tables) to be discovered once more is understood about the relationship being modeled.

Figure 3: Resolving the many-to-many relationship in the logical model

Since the data types (domains) of the associating table are "inherited" from their specification in the Pets and Vets tables, no new domains are introduced in this part of the logical model.

The second thing to notice in the logical diagram from figure 3 is that the constraint regarding the species that a vet may treat is still simply a notation on the diagram - there is nothing inherent in the design that enforces this constraint. This is an example of a database constraint because it involves multiple tables.

In order for the logical design to provide a means of enforcing the constraint, we must somehow derive a valid set of values that VetPatients may take. Recall from the last article that the referenced table in a foreign key relationship gives the referencing table a "pool" of admissible values for the corresponding column(s). In this example, Pets.LicenseNbr and Vets.TaxID provide the pools independently, but fail to enforce the multi-table constraint about the species.

The question then becomes "is there a way to generate a pool of valid values that will enforce the multi-table constraint"?

In figure 4, we introduce a view, "proj [Pets] j [Vets] on {SpeciesName}". The name in the diagram, being somewhat cryptic, bears explanation. This is a hypothetical view that is the result of projecting over (in SQL, "SELECTing") the LicenseNbr and TaxID attributes from the table that results from joining the Pets and Vets table ("[Pets] j [Vets]") on the attribute SpeciesName. In other words, a row will only appear in this view when the SpeciesName attribute for the particular Pet matches that of the particular Vet.

Figure 4: Deriving a valid set of Pet<->Vet combinations via a view

Now that we have this view, we can model a foreign key relationship from the VetPatients to the view. This gives us our "pool" of all valid Pet/Vet combinations. Since the relationship expressed by VetPatients is a subset of all valid combinations, the relationship is modeled as [0..1]:1 from VetPatients to the view.

This serves us well in the logical model, but we quickly run into a roadblock when considering the physical implementation. Namely, SQL does not support declaring foreign keys that reference views.

One possible solution is to simulate the foreign key constraint via triggers. There are a few of drawbacks to this approach, however; 1) triggers must be maintained for each of the three tables involved (and for each data update operation - insert, update, delete), 2) triggers tend to impose a performance penalty for update operations, and 3) triggers, being procedural code, are not directly exposed for scrutiny through the schema as is the case with declarative constraints.

The attribute used for the join predicate, {SpeciesName}, though, gives us a hint as to how we can implement this constraint using foreign keys. If we redundantly store the SpeciesName attribute from the Pets table (renaming it to Pets-SpeciesName) and the same from the Vets table (renaming it to Vets-SpeciesName) in our associating table VetPatients, we end up with the ability to impose an equality constraint on the two attributes which will enforce the business rule (figure 5).

Figure 5:Redundantly storing the SpeciesName attributes from the Pets and Vets tables in VetPatients

In SQL, it is a requirement that the attribute(s) in the referenced table of a foreign key relationship be unique. In the Pets example, however, the only candidate key for the table is LicenseNbr (and is therefore the only choice available as the primary key). In order to provide uniqueness, we must define a reducible superkey that includes the irreducible candidate key attribute, LicenseNbr, plus the (superfluous) attribute SpeciesName. This is represented on the diagram via the "AK" (alternate key) token. An analogous superkey is defined in the Vets table over {TaxID, SpeciesName}. We now have legal "target attributes" for the associating table VetPatients to reference.

Since it is the case that Pets-SpeciesName = Vets-SpeciesName in VetPatients for all rows in a given valid table value, we can eliminate the need for a check constraint by simply "collapsing" them both into one attribute, SpeciesName (figure 6).

Figure 6: One SpeciesName attribute in the associating table

Simsion and Witt [SIMSION, WITT, 2005] call this design an "overlapping foreign key", though their example is based on true candidate keys, and not reducible superkeys.

Listing 1 is the T-SQL script to add the reducible superkeys (declared using UNIQUE constraints) to both the Pets and Vets tables, and to add the associating table VetPatients. Note the foreign key declarations on the associating table.

ALTER TABLE dbo.Pets
  ADD CONSTRAINT uq_Pets_LicenseNbr_and_SpeciesName
    UNIQUE (LicenseNbr, SpeciesName)
GO
ALTER TABLE dbo.Vets
  ADD CONSTRAINT uq_Vets_TaxID_and_SpeciesName
    UNIQUE (TaxID, SpeciesName)
GO

CREATE TABLE dbo.VetPatients(
  LicenseNbr int NOT NULL,
  TaxID int NOT NULL,
  CONSTRAINT pk_VetPatients
    PRIMARY KEY (LicenseNbr, TaxID),
  SpeciesName varchar(24) NOT NULL,
  CONSTRAINT fk_VetPatients_to_Pets
    FOREIGN KEY (LicenseNbr, SpeciesName)
    REFERENCES dbo.Pets(LicenseNbr, SpeciesName),
  CONSTRAINT fk_VetPatients_to_Vets
    FOREIGN KEY (TaxID, SpeciesName)
    REFERENCES dbo.Vets(TaxID, SpeciesName)
)
GO

Listing 1: Pets and Vets superkeys, associating table VetPatients

In continuing with the habit of double-checking that the constraints are working correctly, Listing 2 is provided as a "unit test" script.

Print '
Valid: VetPatients'
INSERT INTO dbo.VetPatients(LicenseNbr, TaxID, SpeciesName)
-- Note that "Fido" sees both of our dog vets
SELECT 1001, 900700002, 'Dog' UNION ALL
SELECT 1001, 900700003, 'Dog' UNION ALL
SELECT 1002, 900700003, 'Dog' UNION ALL
SELECT 1003, 900700001, 'Cat' UNION ALL
SELECT 1004, 900700001, 'Cat'
GO

PRINT '
Invalid: VetPatients - Vet/Pet SpeciesName mismatch'
INSERT INTO dbo.VetPatients(LicenseNbr, TaxID, SpeciesName)
SELECT 1003, 900700002, 'Dog'
GO

Listing 2: Vet-Pet species match unit test

Conclusion

For this article, we explored one of the more complex database (multi-table) constraints from the conceptual model. We discovered a weakness in the SQL implementation of the relational model in that we are unable to declare foreign keys that reference a view in the physical implementation. I presented a workaround to this limitation in the form of a foreign key that references reducible superkeys in the tables to be related via the associating table VetPatients, and we wrapped up with a look at the implementation and test scripts.

Next installment

Second look at the Pets - Vets relationship

References

Simsion, Graeme C., Witt, Graham C., Data Modeling Essentials, Third Edition (Morgan Kaufmann, 2005)

About the author: Troy Ketsdever is a database specialist with over 15 years of commercial softwaredevelopment experience. His main objective and vision is "making the right information available to theright people at the right time".
The author would like to thank Joseph Kelly for his help with reviewing the article drafts for this series, and Logan Schoonmaker for the "pets and vets" sample problem.