This chapter is from the book
This chapter is from the book
This chapter is from the book
Relationships
When I speak of database relationships, I specifically mean how the data in one table relates to the data in another. A relationship between two tables can be one-to-one, one-to-many, or many-to-many.
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).
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. A one-to-many relationship is the most common one between tables in databases.
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. You should try to avoid many-to-many relationships in your design because they lead to data redundancy and integrity problems.
Relationships and keys work together in that a key in one table will normally relate to a field in another, as I mentioned earlier. Once you grasp the basics of unique identifiers and relationships, you can begin to normalize your database.
Tips
Database modeling uses certain conventions to represent the structure of the database, which I'll follow through a series of images in this chapter. The symbols for the three types of relationships are shown in Figure 3.2.
The process of database design results in an ER (Entity Relationship) Diagram, using boxes for tables and the symbols from Figure 3.2.
The term "relational" in RDBMS actually stems from the tables, which are technically called relations.
Figure 3.2 These three stick figures (or variations on these) are used to represent relationships between tables in database modeling.
)