[ Skip menu ]

• Introduction

Basic UML & SQL

• Models

• Classes & schemes

• Rows & tables

• Associations

• Keys

UML design

• Many-to-many

• Many-to-many 2

• Subkeys

• Repeated attributes

• Multivalued attributes

• Domains

• Enumerated domains

- Pizza shop

• Subclasses

• Aggregation

• Recursive associations

• Normalization

SQL technique

• Queries

• DDL & DML

• Join

• Multiple joins

• Join types

• Functions

• Subqueries

• Union & minus

• Views & indexes

[ Contents ]

[ Glossary ]

[ Change log ]

Copyright © 2002–2006, Tom Jewett [ see notice ]

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:

- You can read the values from the table into a combo box, list box, or similar input control on either a Web page or a GUI form. This allows the user to easily select only values that are valid in this domain at this time.

- You can always update the table if new values are added to the domain, or if existing values are changed. This is much easier than modifying your user-interface code or your table structure.

1. 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.

• 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).

2. 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:

• In the scheme, the relationship between Contacts and Hobbies has become many-to-many, instead of one-to-many. This is shown in the scheme by linking an enumeration table to the previous Hobbies table (which now functions like an association class).

Copyright © 2002–2006, by Tom Jewett. Links to this site are welcome and encouraged. Individual copies may be printed for non-commercial classroom or personal use; however, this material may not be reposted to other web sites or newsgroups, or included in any printed or electronic publication, whether modified or not, without specific permission from the author.