Domain Constraints

A domain is the set of logically related values from which the value in a particular column can be drawn. Here are some examples of domains in the bookbiz database:

• The domain of the authors.au_id column is all the Social Security numbers issued by the U.S. government.

• The domain of the authors.city and publishers.city columns is all the cities in the United States; for authors.state and publishers. state, it's all the states in the United States. (Note the assumption that all authors live in the United States.)

• The domain of titles.type is the following set of values: business, popular_comp, psychology, mod_cook, and trad_cook.

• The domain of titles.title_id is the set of values with the following format: The first two characters are capitalized letters of the alphabet from the set BU, PC, PS, MC, TC; the next four characters are integers between 0 and 9, inclusive.

• The domain of titleauthors.royaltyshare is all numbers between 0 and 1, inclusive.

Notice the different kinds of logical relationships among the values in these domains. Some of the domains represent application-determined constraints—that is, business rules and regulations. For example, the constraints on the format of the title ID numbers were determined by someone in the publishing company. The publisher might also decide that the prices of books must be no less than $1.99 and no greater than $99.99; dollars-and-cents amounts between those two values would then be the domain for titles.price.

Other domains are based not on business rules but on physical or mathematical constraints. The values in titleauthors.royaltyshare, for example, represent percentages, so they must be numbers between 0 and 1. As another example, suppose the publisher wanted to record the gender of each author in the database. The domain for that column would be limited by (widely accepted interpretations of) human biology to the values female, male, and unknown.

The descriptions of the domains in the preceding list were deduced from an examination of the values in the bookbiz database. You can use the CHECK constraint in the CREATE TABLE statement to express many of them—lists of values (like the domain for titles.type), ranges (like the domain for titleauthors.royaltyshare), or format (like the domain for titles.title_id).

One last note: Recall from Chapter 7 that if the values in two columns have the same domains, joins between these columns are usually logical. For example, publishers.city and authors.city have the same domain (all cities in the United States); therefore it would be meaningful to join on these columns.