one, many (idea)

Ask a C programmer to count to ten and you'll get "0, 1, 2, 3, 4, 5, 6, 7, 8, 9". Its a far too well known joke. Ask a DBA to count to ten and you'll get "1, many".

With database design a common mistake that is made is in confusing attributes of an objects with relationships between objects. This becomes especially painful when someone starts having an arbitrary number of these "attributes".

Consider the common example of a "person" record in a table. This record would contain the name, birthday, height, weight or whatnot. It is tempting to put "address" or "telephone number" in this record, however, that would be a mistake.

How so? I could just make "phoneno1" and "phoneno2" columns of the table along with "address1" and "address2".

It is quite possible for an individual to have multiple telephone numbers or multiple addresses. How many are you going to put in a table? 1, 2, 3, 10, 20? Lets say you just do two phone numbers, one work and one home number. What about the cell phone? or fax? or pager? So, now you have added 5 phone numbers. Some people have two home, work, or other lines. Lets up that to 10 numbers. Now, consider the SQL necessary to find someone from a telephone number.

SELECT name FROM persontab
WHERE
  homephoneno1  = '555-1234' or homephoneno2  = '555-1234'
  workphoneno1  = '555-1234' or workphoneno2  = '555-1234'
  cellphoneno1  = '555-1234' or cellphoneno2  = '555-1234'
  faxphoneno1   = '555-1234' or faxphoneno2   = '555-1234'
  pagerphoneno1 = '555-1234' or pagerphoneno2 = '555-1234'

Ugly ain't it? Consider also the 'fun' when you want to add another phone number or want to clear phone number 1 from one of those. The logic gets disgusting when it doesn't need to be.

The 'mistake' here is in the failure to realize that a 'phone number' is an object of its own with attributes and there is a relationship between a phone number and a person which likely has attributes of its own.

  persontab   |   phoneperson   |   phonetab
+-----------  | +-------------  | +---------
| personid    | | personid      | | phoneno
| name        | | phoneno       | | device
| height      | | type          |
| weight      | | availstart    |
| birthday    | | availend      |

These three tables demonstrate one example of the persontab and phonetab with a phoneperson relationship table. Here, there is no limit for the number of phone numbers that a person can be related to. Furthermore, information about the phone number (like what type of device it is - voice, data, fax, pager) is stored with the phone number and the attributes of the relationship are stored there (such as the 'work' or 'home' number for 'type' and 'call between' stored as two times). This allows for the 'realization' that between 9-5 this phone number is to be considered a work number and between 5-10 it is a home number (don't call after 10).

Realize this is just one example of how the relationship can be set up and the attributes of each - different designs (and assumptions) will call for different tables (for example this one assumes that a voice line and a fax line aren't the same, or a voice and data). The advantages come when inserting new phone numbers, deleting them, or searching upon this field.

SELECT
  A.name, B.device
FROM
  persontab A,
  phoneperson R,
  phonetab B
WHERE
  A.personid = R.personid and
  R.phoneno = B.phoneno and
  B.phoneno = '555-1234'

Much cleaner and more useful. Indexes on the tables become much more sensible and powerful. In short - it works better.

Thus, if there is more than one of an attribute it should be considered an object of its own with a relationship. Design the tables as appropriate but realize that anything other than a 'something to one' relationship should be considered a 'something to many'.