Database Relationships – Many to Many / One to One

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.

Many-to-Many Relationship

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.

figure 1

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.

Author

<<PK>> author_id [int (11)]

first_name [varchar(20)]

last_name [varchar(20)]

Book

<<PK>> book_id [int (11)]

book_title [varchar(255)]

ISBN [varchar(255)]

version [varchar(10)]

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.

Author_Book

<<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.

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:

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:

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!

One-to-One Relationship

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:

Person

<<PK>> person_id [int (11)]

first_name [varchar(20)]

last_name [varchar(20)]

Drivers_License

<<PK/FK>> person_id [int (11)]

license_number [varchar(20)]

issue_date [datetime]

expiry_date [datetime]

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.

Please help to support these podcasts by filling out a quick survey. This will help keep this information FREE for you to consume and it will help me to keep creating these valuable tutorials at a predictable pace!

Hi Trevor,
Just started with your podcasts (from episode 30). I’m a Java developer since 1997 but even old hands appreciate a refresher (and new tricks) from time to time and your podcasts are perfect for this. Please keep up the great work!

Hi Trevor !
First of all, thanks a lot for your podcast !
Yes, there is in fact a lot of games framework out there written in Java. My favorite one is libGDX (libgdx.badlogicgames.com). Here is an example of what can be done with it : https://play.google.com/store/apps/details?id=be.julien.cheapestshootinggame . Yes it’s a shameless plug 🙂
Anyway, thanks again ! I especially liked your episode on interview questions. Could you perhaps do a new one ? Maybe with more advanced questions ?
Julien

Great to know that you loved the podcasts on the interview questions. I will definitely record another episode focusing on even more questions, since those two episodes were very popular. Not sure when I’ll get around to it, but I’ll try to make it in the near future (perhaps once I’m done my current tutorials focusing on MySQL)

Hi Trevor,
This was really nice, easy and real piece of cake 🙂
but one think confusing me, why you typed this
“insert into author_book (author_id, book_id) values (14232, 9127329298);”
sql commend twice in first code block.
I think this create duplicate entry which is not good and also confuse other readers.
but overall it was really good
Thank you
Qasim Rafique

One observation, in one-to-one relationship example, license_number is not unique in the Drivers_License table.So there is possibility of same license number being assigned to multiple persons, breaking one-to-one relationship.

You need to rephrase this: ” Can one person have more than one drivers license? In the case of North America, the answer is no,”

The real answer is Yes, in North America you can have 2 or 3 Drivers Licence. I have a valid licence from the state of Florida, a Canadian driver licences (where I live now) and Mexican drivers licences (still valid).