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:

Contact phonebook class diagram

• Again, this might seem like a reasonable design until you look at the data (omitting the street and zip to reduce table width):

Contact phones
firstName	lastName	homePhone	workPhone	cellPhone	fax	pager
George	Barnes	562-874-1234		310-999-3628
Susan	Noble	562-975-3388	714-847-3366
Erwin	Star				714-997-5885	714-997-2428
Alice	Buck		562-577-1200	562-561-1921
Frank	Borders	714-968-8201
Hanna	Diedrich			562-786-7727

• There are at least two very large problems here:

- None of our contacts are going to have all of the phone numbers that we've provided for. In fact, most contacts will have only one or two numbers—leaving most of these fields blank, or NULL. Remember that NULL is a special constant value in database systems that means “this field doesn’t have any value assigned to it.” It’s not the same as a zero length string or the number zero. In general, we want to eliminate unnecessary NULL values that might occur as a result of our design—and the NULL values in this table are definitely unnecessary.

- No matter how many phone number fields we provide (five of them here), sooner or later someone will think of another kind of phone number that we need. With the model above, we would have to actually change the table structure to add another field. Any information like this that might change should be represented by data in a table rather than by the table structure.

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

Contact phonebook class diagram, revised

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.

1. Remove all of the phone number fields from the Contacts relation. Create a new scheme that has the attributes of the PhoneNumber structure (phoneType and number).

2. The Contacts relation has now become a parent, so we should add a surrogate key, contactID. Copy this into the new scheme, so that we can associate the phone number with the person it belongs to. There is now a one-to-many relationship between Contacts and PhoneNumbers. Notice that this is the opposite of the relationship between Contacts and ZipLocations.

3. To identify each phone number, we need to know at least who it belongs to and what type it is. However, to allow for any combination of contacts, phone types, and numbers, we will use all three attributes of the new scheme together as the primary key. It’s not a parent, so we aren’t concerned about size of the PK.

Contact phonebook relation scheme

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

Contacts
contactid	firstname	lastname	street	zipcode
1639	George	Barnes	1254 Bellflower	90840
5629	Susan	Noble	1515 Palo Verde	90840
3388	Erwin	Star	17022 Brookhurst	92708
5772	Alice	Buck	3884 Atherton	90836
1911	Frank	Borders	10200 Slater	92708
4848	Hanna	Diedrich	1699 Studebaker	90840

Phone numbers
contactid	phonetype	number
1639	Home	562-874-1234
1639	Cell	310-999-3628
5629	Home	562-975-3388
5629	Work	714-847-3366
3388	Fax	714-997-5885
3388	Pager	714-997-2428
5772	Work	562-577-1200
5772	Cell	562-561-1921
1911	Home	714-968-8201
4848	Cell	562-786-7727

Employee dependents

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:

Employee dependent 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.

Employee dependent relation scheme

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.