Toward Integrity

Modeling Constraints in the Database

Article 1: Introduction and the Universe of Discourse

In this series of articles, 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.

A guiding architectural principle for this series is to utilize the database layer to enforce these business rules. By modeling the rules as database constraints, we realize many advantages over other approaches (e.g., enforcing rules in the application layer). The implementation of the rules ends up being more concise, less prone to bugs, and more resilient in the face of additional applications--usually unforeseen during the initial project. These benefits accrue partly due to the declarative nature of SQL, which allows us to express the rules in a language closer to the business user's level of intent.

Since the focus on this series is logical correctness, I will often perform a translation to the physical implementation that, in normal contexts, would be considered "questionable". This is because I want to reflect the logical model as much as possible in order to highlight the interactions present at that layer. In other words, we favor simplicity and logical model fidelity over performance.

The tone of the series will be somewhat informal - i.e., using the terms "table", "row", "column", etc. rather than the more formal counterparts. I trust that this will not hinder understanding of the concepts presented.

For this first installment, I would like to lay out the conceptual model including the business rules that apply for the problem space. The running example will be veterinary doctors and the pets they treat. We assume a requirements analysis has produced a valid conceptual (business) model that will be the basis for the implementation, introduced next.

Conceptual Model:

The entities in our universe of discourse include:

"Species" - a classification of animal. For this example, think of species in terms of a preschool child's understanding of the concept; "cat", "dog", "horse", "fish", etc. In other words, don't get too hung up on this simplification of biological taxonomies.

"Vet" - a licensed provider of medical care for animals within the jurisdiction of interest

"Pet" - a particular animal within the jurisdiction of interest.

"Dog" - a canine pet, "Cat" - a feline pet.

The conceptual model (figure 1) is diagramed using Barker notation and Hay's [HAY, 1995] convention for model organization.

Figure 1: Conceptual model

Although not comprehensive in the representation of the business rules for the application domain, there is quite a bit of information we are able to discern from the graphics and notes on the drawing.

In the diagram's notation, a dotted line "exiting" an entity box is to be read as "may", whereas a solid line exiting an entity box is to be read as "must". The crows-feet notation is used to indicate a "many" side in the relationship. The relationships represented on the diagram in Figure 1 are as follows:

• A species may be seen by zero, one, or more vets / A vet is licensed to see one and only one species of animal (an unreasonable restriction in reality, but it will serve us well for illustration purposes).
• A species may be a classification for zero, one, or more pets / A pet must be an example of one particular species.
• A pet may be the patient of zero, one, or more vets / A vet may provide healthcare for zero, one, or more pets. Note that this is a "many-to-many" relationship. We'll resolve this concept by introducing an associative table in a later article.

Further details from the diagram include:

• A vet is assigned an "Experience Rating". For this example, the valid values are "Junior" and "Senior".
• A pet is identified by a "License Number" (which is unique within our example jurisdiction, regardless of the species of pet).
• A pet is assigned a "Disposition Rating", which may take either "EasyGoing" or "Difficult" as a value.
• For dogs, we track whether or not they are "AKC Registered". For cats, our concern is whether or not it's been "Declawed".
• A pet may only see a vet if that vet is licensed to treat that species of pet.
• A vet may only treat a pet with a "Difficult" disposition rating if the vet has a "Senior" experience rating.

As we move to the logical model, certain other details not represented on the conceptual model diagram will come to light.

Classification of Constraints

In various writings [DATE 1998], C.J. Date has proposed the following classification scheme for database constraints:

• Domain constraints: Constraints that specify the legal values for the various domains (datatypes) within the database.
• Column (attribute) constraints: These simply specify which domain (datatype) the values within the columns belong to.
  Note: Since SQL has such poor support for the concept of domains, column and domain constraints are often conflated both in description and in implementation. Unfortunately, we inherit this problem when working through practical examples. Expect to see domain constraints loosely translated to the closest available datatype, and using CHECK constraints where feasible as we examine the implementation of the concepts in this series.
• Table (relation variable) constraints: A table constraint specifies the valid values that a table may take. Although this applies to the entire table "at once" -- i.e., the set of all rows (tuples) that make up the current value of the table (relation variable) -- certain table constraints can be checked against individual rows. An example would be any CHECK constraint that references two or more columns within the table. Self-referencing foreign keys are another example of table constraints.
• Database constraints: Those that reference more than one table. The most familiar example is a foreign key constraint (excluding a self-referencing foreign key).

As we work through each subsection in the example, we will consider the constraints being modeled at the logical level in light of the above classification scheme.

Conclusion

Thus far we have taken a high-level look at the problem space and the conceptual model that derives from a hypothetical analysis of the requirements for information storage. In the next article, we will delve into some of the more straightforward constraints.

The the next article, I will be tackling some simple constraints with this design.

References

Hay, David C., Data Model Patterns: Conventions of Thought (Dorset House, 1995)
Date, C.J., Relational Database Writings 1994-1997 (Addison-Wesley, 1998)

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.