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. Figure 3.2. These three stick figures (or variations on these) are used in database models to represent the relationships between tables. 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. Figure 3.3. There is a one-to-one relationship between Social Security numbers and United States citizens. 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. Figure 3.4. There is a one-to-many relationship between people and gender. Each person only has one gender, but each gender can apply to multiple people. 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. Figure 3.5. There is a many-to-many relationship between albums and artists. 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.
|
Tips