Design pattern: repeated attributes (the phone book)
The contact manager example from our preceeding discussion of subkeys is also an excellent illustration of another problem that is found in many database designs.
• Obviously, the contacts database will need to store phone numbers in addition to addresses. A typical simplistic model, even after fixing the zip code problem, might look something like this:
• Again, this might seem like a reasonable design until you look at the data (omitting the street and zip to reduce table width):
• There are at least two very large problems here:
• In fact, we don’t have five single attributes here. We have a repeated attribute, phone, that also has an attribute of its own that tells us what type of number it is (home, work, cell, and so on). In effect, it’s a class within a class. Some database textbooks call this structure a weak entity, since it can’t exist without the parent entity type.
• In UML, we can show multiplicity of attributes the same way we show multiplicity of an association (for example [0..*]). We can also show the data type of an attribute, which in this case is a structure (PhoneNumber). We have listed the structure attributes below in parentheses.
If we try to represent information this way in the Contacts table, we’ll end up with a subkey that we obviously don’t want. We have to create a new table in much the same way as we did for the zip locations.
• The Contacts table now looks like it did before we added the phone numbers (with the addition of the contactID). The new PhoneNumbers table can be joined to the Contacts on matching contactID pk-fk pairs to provide all of the information that we had before.
|5629||Susan||Noble||1515 Palo Verde||90840|
The modeling technique shown above is useful where the parent class has relatively few attributes and the repeated attribute has only one or a very few attributes of its own. However, you can also model the repeated attribute as a separate class in the UML diagram. One classic textbook example is an employee database. The employee class represents “a person who works for our company”; each employee has zero or more dependents. The dependent is conceptually a repeated attribute of the employee, but can be described separately as “a person who is related to the employee and may receive health care or other benefits based on this relationship.” We can represent this fact in the class diagram:
• The relation scheme is a standard one-to-many; the PK of the many-side relation will have to include both the fk from the parent and one or more local attributes to guarantee uniqueness.
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.