Understanding Relationships

Pretty much every database consists of multiple tables, where each table stores one segment of the application's data. In a normalized database, some tables will be associated with other tables (not all will, though). For example, going back to a hollywood database, there would be a relationship between a table that stores directors and another that stores information about specific movies. The association between two tables is called a relationship, of which there are three kinds: one-to-one, one-to-many, or many-to-many. The representative symbols for the three types of relationships are shown in Figure 3.2.

The relationship is one-to-one if one and only one item in Table A applies to one and only one item in Table B (e.g., each U.S. citizen has only one Social Security number, and each Social Security number applies to only one U.S. citizen; no citizen can have two Social Security numbers, and no Social Security number can refer to two citizens). Figure 3.3 shows how this would be diagrammed.

A relationship is one-to-many if one item in Table A can apply to multiple items in Table B. The terms female and male will apply to many people, but each person can be only one or the other (Figure 3.4). A one-to-many relationship is the most common one between tables in a database.

Finally, a relationship is many-to-many if multiple items in Table A can apply to multiple items in Table B. For example, a record album can contain songs by multiple artists, and artists can make multiple albums (Figure 3.5). You should try to avoid many-to-many relationships in your design because they lead to data redundancy and integrity problems. They'll be found and fixed during the normalization process.

Relationships and keys work together in that the keys serve as the connection between related tables. The primary key in one table will normally relate to a foreign key in another.

Now that you have the basics of keys and relationships, you can begin to normalize your database.