Toward Integrity

Modeling Constraints in the Database

Article 4: Second 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, Part 2, and Part 3.

This is the fourth 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, refining our design from the third article.

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

In today's article, we will model and implement the following business rules:

• A pet may be the patient of zero, one, or more vets / A vet may provide healthcare for zero, one, or more pets.
• A vet may only treat a pet with a "Difficult" disposition rating if the vet has a "Senior" experience rating.

We start with a logical model similar to last article's (figure 3), where we introduce the association table "VetPatients" in order to resolve the many-to-many relationship between Pets and Vets. For this article, we focus on the business rule regarding the vet's experience rating and which types of pets they are allowed to treat.

Figure 3: Resolving Pets-Vets relationship with an associating table

Since this constraint involves multiple tables, we are again dealing with modeling a database constraint. As before, we can introduce a view in order to provide a "pool" of valid sets of {LicenseNbr, TaxID} attribute values (figure 4).

Figure 4: Using a view to generate a valid pool of values for the associating table

This view, however, is more than a simple join expression. There is an additional constraint on the view. Specifically, legal rows must conform to the constraint that if DispositionRating is"Difficult", the ExperienceRating must be "Senior". Note how rather than indicating a particular attribute to be joined on, a note is added to the diagram describing the rule.

It would certainly be possible to resolve this in a similar manner as we did for the "SpeciesName matching" rule from the last article - by redundantly storing the relevant attributes in the associating table and modeling a foreign key relationship to reducible superkeys in each of the referenced tables. But I'd like to present another way of looking at this. More tools in the toolkit is always a good thing.

Take a look at the T-SQL that underlies our hypothetical view:
SELECT p.LicenseNbr, v.TaxID
FROM dbo.Pets p
INNER JOIN dbo.Vets v
ON (p.DispositionRating = 'Difficult' AND v.ExperienceRating = 'Senior')
OR p.DispositionRating <> 'Difficult'

The join predicate here provides a filter for including valid {LicenseNbr, TaxID} combinations. If we, instead, express the negation of this predicate, we obtain all known invalid {LicenseNbr, TaxID} combinations. I'll walk through the transformation to the negation of this predicate here for clarity:

First, the predicate is somewhat "wordy" as currently expressed. The underlying rule can be stated more simply like this...
IF (DispositionRating = 'Difficult') THEN (ExperienceRating = 'Senior')

SQL, however, lacks an implication ("IF...THEN...") connective, so we must use the appropriate combination of AND, OR, and NOT to arrive at the SQL-friendly statement...
NOT (DispositionRating = 'Difficult') OR (ExperienceRating = 'Senior')

To obtain the negation, we start by enclosing the whole statement and applying "NOT"...
NOT (NOT (DispositionRating = 'Difficult') OR (ExperienceRating = 'Senior'))

And finally simplify the expression by distributing the "NOT"...
DispositionRating = 'Difficult' AND ExperienceRating <> 'Senior'

The full SQL statement to give us the disallowed combinations of Pets-Vets is:
SELECT p.LicenseNbr, v.TaxID
FROM dbo.Pets p
INNER JOIN dbo.Vets v
ON (p.DispositionRating = 'Difficult' AND v.ExperienceRating <> 'Senior')

So if there is a {LicenseNbr, TaxID} combination in VetPatients that corresponds to one of the invalid combinations from the pool, our database is in an errant state.

The next piece of the puzzle to solve, then, is to detect when this invalid state is true. Recall that a foreign key models a subset relationship; the referencing table's value projected over the foreign key attributes holds a subset of the table value from the referenced attributes. It follows that if we look at the results of a join between the referencing and referenced tables (or views), the cardinality of that resultset is zero or more. But since our hypothetical view contains only invalid values, we need to ensure that the cardinality of such a join is always zero, but never more than zero.

For users of SQL Server Enterprise (and Developer) editions, we can leverage indexed views for the mechanics that enable this constraint enforcement.

A join between the VetPatients, Vets, and Pets tables that includes the negated rule predicate gives us a view for which the cardinality should always be zero (figure 5).

Figure 5: View of disallowed DispositionRating/ExperienceRating combinations

We can enforce a cardinality of zero by using a cartesian join of this expression with a table that is always guaranteed to contain more than one row. For the implementation example, I join to a table of base-10 digits which is populated with digits 0 through 9. This forces duplication in any resultset that satisfy the condition. After that, it's a simple matter of declaring a uniqueness constraint on the view itself (i.e., a unique index).

Listing 1 shows the DDL statements that create the view and index as well as the "helper" Digits table.

-- Create and populate a "Digits" table if it doesn't yet exist...
IF NOT EXISTS(
    SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'Digits'
)
BEGIN
    CREATE TABLE dbo.Digits(
      Digit bigint NOT NULL
        CONSTRAINT pk_Digits
        PRIMARY KEY CLUSTERED
        CONSTRAINT ck_Digits_Digit
        CHECK (Digit BETWEEN 0 AND 9)
    );
    INSERT INTO dbo.Digits(Digit)
    SELECT 0 UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9;
END
GO

CREATE VIEW dbo.v_VetPatients_Disallowed
WITH SCHEMABINDING
AS
SELECT vp.LicenseNbr
FROM dbo.VetPatients vp
INNER JOIN dbo.Pets p
    ON vp.LicenseNbr = p.LicenseNbr
INNER JOIN dbo.Vets v
    ON vp.TaxID = v.TaxID
   AND p.DispositionRating = 'Difficult'
   AND v.ExperienceRating <> 'Senior'
CROSS JOIN dbo.Digits d
GO

CREATE UNIQUE CLUSTERED INDEX cdx_DifficultPets_MustSee_SeniorVets
    ON dbo.v_VetPatients_Disallowed(LicenseNbr)
GO

Listing 1: Creating the indexed view of dissallowed Pet/Vet combinations

Listing 2 has the "unit test" script for checking the constraint enforcement. Since the current table value for VetPatients consists of all valid rows from the last installment, the unit test only checks an invalid entry.

PRINT '
Invalid: VetPatients - Disposition/Experience ratings combination disallowed'
INSERT INTO dbo.VetPatients(LicenseNbr, TaxID, SpeciesName)
SELECT 1002, 900700002, 'Dog'
GO

Listing 2: Unit test for the ERating/DRating rule

Conclusion

In this article, we took a look at another database (multi-table) constraints from the conceptual model. Rather than attacking the problem from the point of view of the valid set of Pet-Vet combinations, we turned the problem around and declared a construct that exposes the invalid set of Pet-Vet combinations. We used SQL Server's indexed view facility to enforce this in the physical implementation. And, as usual, we wrapped up with a look at the implementation and test scripts.

Next installment

Generalization/specialization tables and wrap-up.

About the author: Troy Ketsdever is a database specialist with over 15 years of commercial software development experience. His main objective and vision is "making the right information available to the right 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.