Design pattern: multivalued attributes (hobbies)
Attributes (like phone numbers) that are explicitly repeated in a class definition aren’t the only design problem that we might have to correct. Suppose that we want to know what hobbies each person on our contact list is interested in (perhaps to help us pick birthday or holiday presents). We might add an attribute to hold these. More likely, someone else has already built the database, and added this attribute without thinking about it.
• We’ve made this example obvious by using a plural name for the attribute, but this won’t always be the case. We can only be sure that there’s a design problem when we see data in a table that looks like this:
| contactid | firstname | lastname | hobbies |
|---|---|---|---|
| 1639 | George | Barnes | reading |
| 5629 | Susan | Noble | hiking, movies |
| 3388 | Erwin | Star | hockey, skiing |
| 5772 | Alice | Buck | |
| 1911 | Frank | Borders | photography, travel, art |
| 4848 | Hanna | Diedrich | gourmet cooking |
• In this case, the hobby attribute wasn’t repeated in the scheme, but there are many distinct values entered for it in the same column of the table. This is called a multivalued attribute. The problem with doing it is that it is now difficult (but possible) to search the table for any particular hobby that a person might have, and it is impossible to create a query that will individually list the hobbies that are shown in the table. Unlike the phone book example, NULL values are probably not part of the problem here, even if we don’t know the hobbies for everyone in the database.
• In UML, we can again use the multiplicity notation to show that a contact may have more than one hobby:
As you should expect by now, we can’t represent the multivalued attribute directly in the Contacts relation scheme. Instead, we will remove the old hobbies attribute and create a new scheme, very similar to the one that we created for the phone numbers.
• The relationship between Contacts and Hobbies is one-to-many, so we create the usual pk-fk pair. The new scheme has only one descriptive attribute, the hobby name. To uniquely identify each row of the table, we need to know both which contact this hobby belongs to and which hobby it is—so both attributes form the pk of the scheme.
• With data entered, the new table looks similar to the PhoneNumbers. It can also be joined to Contacts on matching pk-fk contactID pairs, re-creating the original data in a form that we can now conveniently use for queries.
| contactid | hobby |
|---|---|
| 1639 | reading |
| 5629 | hiking |
| 5629 | movies |
| 3388 | hockey |
| 3388 | skiing |
| 1911 | photography |
| 1911 | travel |
| 1911 | art |
| 4848 | gourmet cooking |
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.