The first rule we look at is "No repeating groups." We will have to create a separate table for phone numbers because we have more than one for some people. Each phone number has a person it belongs to, the location, and the number.
The phone data for Mary Jones will look like this:
| ID | Location | Number |
|---|---|---|
| MaryJ | home | 301-456-1234 |
| MaryJ | cell | 301-933-0822 |
This table will have both ID and Location as the key. We need both for the key because otherwise we would have duplicates.
We will do the same for the birthday and anniversary:
| ID | Event | Date |
|---|---|---|
| MaryJ | birthday | 0000-05-16 |
| MaryJ | anniversary | 2006-02-14 |
What about the baby's birthday? We can either add Jenny as another person in the person table with her own nickname, JennyJ, or add the birthday under MaryJ. There is no one correct answer to this.
What about the address? We can either add it to the record for a person or create an address table. It is simpler to add it to the person table. One advantage to creating a separate address table is that we only need one entry for Mary, John and Jenny. If Mary gives us a change of address we can update that entry and it would be the same for all three people. Another advantage to creating a separate table for addresses is that we could then have more than one address per person such as home and work.