Design pattern: enumerated domains

Attribute domains that may be specified by a well-defined, reasonably-sized set of constant values are called enumerated domains. You might know all of the values of the domain at design time, or you might not. In either case, you should keep the entire list of values in a separate table. Tables that are created for this purpose might be called enumeration tables, dictionary tables, lookup tables, or domain-control tables or entities in some textbooks and database software systems. Once in the database, they are no different from any other table; PKs and FKs link them to other tables as always. There are a number of ways to design these tables, from which the designer can choose the most appropriate for a particular attribute in a particular class.

Many students ask if this technique will create too many tables and query joins. The answer is: “No.” Design the database as well as you can—if you have to “break the rules” later for faster performance, you can always do so. In most designs, the drawbacks of any additional table or tables are overwhelmed by their advantages:

Single valued, enumerated domain

In our earlier ZipLocations example, the state attribute clearly fits the definition of an enumerated domain. In UML, we can simply use a data type specification to show this, without adding a new class type.

UML class diagram including a ZipLocation class with a highlighted attribute
        state: Enumerated
Enumerated attribute ZipLocation's state

The relation scheme will show the table that contains the enumerated domain values. This table might have a single attribute, or it might have two attributes: one for the true values and one for a substitute key. Notice that the true values always form a candidate key of the table.

For states, the second approach provides both the full name of the state and the U.S. Postal Service abbreviation—a real benefit when it is time to design the user interface. Any time that you can find an existing enumeration (external key), you should use it instead of making up your own values. Besides the USPS state codes, examples include international airport designators (like LAX, defined by the International Civil Aviation Organization) and web top-level domains for countries (like CH or DE, called ccTLDs and defined by the Internet Assigned Numbers Authority).

Relation scheme for previous figure, showing that the enumerated domain state maps to its own table
Mapping the enumerated domain of attribute state.

Multivalued, enumerated attribute

Multivalued attributes (for example, hobbies) might also have enumerated domains. We can show this in the class diagram exactly as we did with a single-valued attribute:

UML class diagram including a multivalued attributed hobbies that has an Enumerated domain
A multivalued attribute hobbies that has an enumerated domain

In the scheme, the relationship between Contacts and Hobbies has become many-to-many, instead of one-to-many. This can be observed more easily in the scheme by linking an enumeration table to the previous Hobbies table.

Relation scheme showing the multivalued attribute with enumerated domain maps to
        an additional scheme that holds the values in the enumerated domain
Mapping of the multivalued attribute that has an enumerated domain.