Types of Relationships
Pages: 1, 2
Many-to-many
Finally, there is the many-to-many table. This relationship is a little more complex than the one-to-many because, in addition to the two tables of data, we need another table to join the two tables of interest together. That's right, we're adding a table to the database -- but it is a simple table and saves us lots of effort down the road. As an example, let's say you want to add the ability to search for CDs by the musicians on any given song. From the musician side, you have one musician related to many songs.
Musician Table
| MusicianID | MusicianName |
| 44 | Paul McCartney |
Song Table
| SongID | MusicianID | SongName |
| 200 | 44 | Sgt. Pepper's Lonely Heart's Club Band |
| 201 | 44 | Ebony and Ivory |
But from the song side, you potentially have a song related to many musicians. The following visual represents that situation.
Song Table
| SongID | SongName |
| 200 | Sgt. Pepper's Lonely Heart's Club Band |
Musician Table
| MusicianID | SongID | MusicianName |
| 43 | 200 | John Lennon |
| 44 | 200 | Paul McCartney |
These two tables work individually, but when you try to put them together you end up with this mish-mash table.
Song Table
| SongID | MusicianID | SongName |
| 200 | 43 | Sgt. Pepper's Lonely Heart's Club Band |
| 200 | 44 | Sgt. Pepper's Lonely Heart's Club Band |
| 201 | 44 | Ebony and Ivory |
Musician Table
| MusicianID | SongID | MusicianName |
| 43 | 200 | John Lennon |
| 44 | 200 | Paul McCartney |
| 44 | 201 | Paul McCartney |
This has saved us nothing -- in fact, it has complicated the structure by introducing lots of redundant data to manage. The way to handle this situation is to create two one-to-many relationships involving a linking table which we'll call Song_Musician, since it links those tables. We create a one-to-many from Song to Song_Musician since one song will have 0-N musicians and then another one-to-many from Musician to Song_Musician since any one musician will be in one or more songs. The results look like the following:
Musician Table
| MusicianID | MusicianName |
| 43 | John Lennon |
| 44 | Paul McCartney |
Song_Musician Table
| SongID | MusicianID |
| 200 | 43 |
| 200 | 44 |
| 201 | 44 |
Song Table
| SongID | SongName |
| 200 | Sgt. Pepper's Lonely Heart's Club Band |
| 201 | Ebony and Ivory |
This time around, all of the redundant data is in the Song_Musician table, which is only two columns of integers. Any changes to the structure of the Song or Musician table remain independent of their relationship, which is precisely what we're after.
Next steps
After our whirlwind two-week tour of database relationships, we're now ready to jump into the SQL JOIN statement, which is the core of what relational databases are all about. Until then, feel free to contact me with comments, questions, and criticisms aboutSQL.
John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.
Read more aboutSQL columns.
Return to ONLamp.com.
