A person has an address book and would like to create a normalized database to store the information. Looking through the address book she finds the following information for her friends:
Names: The name is often used to alphabetize (sort) the contacts. If you want to be able to sort the names the names should either be Last, First or use separate fields for First and Last names. People have just 1 birthday, but we may not have the birthday for everyone.
Birthdate: We would say that the relationship is 0:1 (zero to one) meaning that a person can have either 0 or 1 birthday. If we say that the relationship is 1:1 (one and only one) then the system should call it an error if the birthday is missing.
The email address can be treated the same as the birthday unless you have more than one email address for some of the contacts, for instance personal and work. Phone numbers are what is called a 0:M (zero to many) relationship. We should never have a table in the database with fields for cell phone, home phone, office phone, etc. For one thing, we would never be able to create a reverse directory where the table is sorted by phone number. Second, this becomes unwieldy. We would end up with fields for spouses phone, fax number, sat phone, etc. Instead we will create a separate table for the phone numbers.
Spouse is another field that has a relationship of 0:1 (zero to one), not everyone has a spouse. If you have additional information for the spouse, the spouse should have a separate entry (row).
The address should be separate field for street, city, state and postal code. Most databases have both street1 and street2 and many would also have a field for the country. You can add a field for the contact to hold information about how you know the person such as “friend”,”medical”,”realtor”, etc.
These are the tables that we will have: Contact
Birthday
Spouse
Street1
Street2
City
State
Postal
Relation
Smith,Jane
1/21/1930
12 Bank St.
Ithaca
NY
14850
friend
Banks, Robin
5/1/1976
Banks,Sandy
255 Linden
Apr 419
Brooklyn
11226
college
Phones
Phone
Number
cell
(259) 065-3221
home
(259) 108-2965
work
(301) 933-4901
(524) 123-9087
The queries of the database can be used to display a list in order by name, or phone number or the display can select only the ones from college. It would be easy to display a list such as the following; Smith,Jane: January 21, 1930 Cell: (259) 065-3221 Home: (259) 108-2965 Work: (301) 933-4901 Banks,Robin: May 1, 1976 Cell: (524) 123-9087
You could also use these tables to print address labels.
Database Design