In a previous post we learned a whole bunch about the most common database relationship that exists, the one-to-many relationship.
In this post we will be expanding on the topic of database relationships and touch on two that are less common but just as useful.
The many-to-many database relationship is used when you are in the situation where the rows in the first table can map to multiple rows in the second table… and those rows in the second table can also map to multiple (different) rows in the first table.
If that quick and dirty explanation is a bit too general, let's take a look at a real world example!
Let's think of books and authors and decide what that relationship looks like.
The first question we ask is: Is there indeed a need for a “Many” side of the relationship?
Which means: can an author create “many” books? The answer is definitely Yes!
The second question we ask is: Does the table (Object) on the “Many” side actually only map to 1 item in it’s related table?
Which means: can a book only be written by one author? The answer here is No many books have been published by multiple authors!
So this means that we're definitely in the many-to-many arena with this relationship.
How do you create a Many-to-Many relationship in SQL?
This is where things get slightly different from the more popular One-to-Many relationship.
A good design for a Many-to-Many relationship makes use of something called a join table. The term join table is just a fancy way of describing a third SQL table that only holds primary keys. You see, it's easy to draw out this relationship on paper, you can see an example of it in figure 1 above. When it comes to creating this relationship in terms of SQL tables, it's just one step more complicated.
First let's outline what the author and book tables could look like in SQL.
|<<PK>> author_id [int (11)]|
|<<PK>> book_id [int (11)]|
Okay, so this is how the book and author tables could look like, but there's no relationship defined yet! So let's create one… since this is a Many-to-Many relationship and because I already mentioned that you'll need to use a join table when implementing a Many-to-Many relationship, let's see what this join table should look like.
|<<FK>> author_id [int (11)]|
|<<FK>> book_id [int (11)]|
A few things to note here:
- By convention, the name of this join table is usually just the combination of the two tables of the many-to-many relationship. In this case it's just author_book, which implies that this is a join table since it's using the name of two existing tables joined by an underscore.
- This join table only contains the primary keys from the author and book tables. Since this join table is referring to primary keys that don't actually belong to the join table, they are actually referred to as foreign keys.
- Sometimes it's useful to assign a primary key column to a join table ( i.e. author_book_id [int (11)] )
So now that we've created this join table, we will be able to easily create ANY relationship by inserting the appropriate rows into the join table. For example, if author “Trevor Page (author_id=14232)” created the book “How to Program with Java (book_id=9127329298)” then you could just insert the following row into the join table:
insert into author_book (author_id, book_id) values (14232, 9127329298);
So this will create a relationship between “Trevor Page” and “How to Program with Java”, but let's say Trevor Page publishes another book (book_id=9315619872) and has some help from another author (author_id=14585) who also happens to have authored another book (book_id=8181225133), we can just insert those values into the join table to create that many-to-many relationship:
insert into author_book (author_id, book_id) values (14232, 9315619872); insert into author_book (author_id, book_id) values (14585, 9315619872); insert into author_book (author_id, book_id) values (14585, 8181225133);
So now we have author “Trevor Page” who owns two books. One of those books has a second author, and that second author also owns a book that “Trevor Page” does not.
Piece of cake right?
Not really I suppose, I had a lot of trouble figuring out the intricacies of the many-to-many relationship at first. So don't worry if you don't fully follow it, it'll come with time and practice!
Okay, so let's switch gears to the easiest relationship to understand. That's the One-to-One relationship. This one should hopefully be self-explanatory at this point, but if it isn't, I shall explain.
A One-to-One relationship means that you have two tables that have a relationship, but that relationship only exists in such a way that any given row from Table A can have at most one matching row in Table B.
A real world example of this could be the relationship between a person and a drivers license. Can one person have more than one drivers license? In the case of North America, the answer is no, any given person cannot have more than one drivers license. Well then, what's the reverse case? Can one particular drivers license be owned by more than one person? Again, in North America's case, the answer to that is no as well. One drivers license is assigned to one person, and ONLY one person.
So this means we can a One-to-One relationship. If I were to pick out ANY drivers license from a huge pile of drivers licenses, any individual license would point me back to ONE person in particular.
How do you create a One-to-One relationship in SQL?
The trick to creating a one-to-one relationship in SQL is to identify which table is on the “right hand side” or “child” of the relationship. This is usually done by deciding which object can exist without the other.
So ask yourself this question: Can a person exist without a drivers license? The answer is yes (I would hope)… then, can a drivers license exist without a person? I would say no, you cannot create a drivers license that doesn't belong to someone, it just wouldn't make sense.
So this is much like a parent/child relationship right? The parent in this case is the Person, and the child is the drivers license. You'll find that with the One-to-One relationship, this will be the case most of the time.
Since we've established that the drivers license is the “child” of this particular one-to-one relationship, we can move forward with our table design.
When designing the SQL tables for the one-to-one relationship, you'll need to make sure that the “child” table's primary key, is also the foreign key of the “parent” table. So this means that the drivers license table's primary key, should actually be the person table's key. It will look something like this:
|<<PK>> person_id [int (11)]|
|<<PK/FK>> person_id [int (11)]|
So the important thing to note here is that the drivers_license table does NOT have it's own drivers_license_id column, as that would break the design for a true one-to-one relationship.