≡ Menu

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.

<<PK>> author_id [int (11)]
first_name [varchar(20)]
last_name [varchar(20)]
<<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.

<<FK>> author_id [int (11)]
<<FK>> book_id [int (11)]

A few things to note here:

  1. 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.
  2. 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.
  3. 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!

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:

<<PK>> person_id [int (11)]
first_name [varchar(20)]
last_name [varchar(20)]
<<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!
Keep Podcast Free


Also be sure to check out the additional resources:

Java Modifiers

SQL Subquery

Constructors Java Tutorial 

{ 27 comments… add one }
  • scott Wilson-Billing February 20, 2014, 3:51 am

    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!


  • Steve April 22, 2014, 6:56 pm

    From the podcast… the question came up with “what happened to PhoneGap?” it became Apache Cordova https://cordova.apache.org/

  • Julien Bertozzi May 16, 2014, 9:11 am

    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 ?

    • Trevor Page May 16, 2014, 11:16 am

      Hi 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)

  • Grace Joseph January 28, 2015, 1:53 am

    thanks for the well detailed post. all these relationships was easy to understand. only problem I had was the sql table not displaying well since I’ve browsing from a mobile device.

    • Trevor Page February 7, 2015, 11:02 am

      It’s tough to really “nail it” with mobile these days. Sorry it didn’t really display properly, but you can always come back to howtoprogramwithjava.com when you get home on your computer 🙂

  • Chandan July 27, 2015, 11:59 am

    Thanks a lots….!! It was really informative.

  • qasim October 2, 2015, 9:21 am

    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

    • Trevor Page October 27, 2015, 9:02 am

      Great catch! I’ve updated the post to reflect this change.

  • Anonymous October 14, 2015, 1:55 pm

    Very Nice explanation

  • saga October 27, 2015, 11:10 pm

    This was great help!

  • Balu April 13, 2016, 1:54 pm

    I was struggling to understand how to decide Parent& Child tables. Then i got the line “which object can exist without the other” and understood the basic concept.

    Thanks for posting a practical and lovely example. Good Job!


  • Prashant April 15, 2016, 10:12 am

    Hello Trevor,
    Thank you for simple and nice explanation. Keep it up!

    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.

    As an alternate option – can we design Person Table as (person_id (PK), first_name, last_name_license_number(FK)) and Drivers_license Table as (license_number (PK), issue_date, expiry_date) ?


  • Lashit May 23, 2016, 6:28 pm

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

  • Anonymous December 4, 2016, 8:18 am

    Hi Trevor ,

    Your thought process for explaining things is really good .Keep up the good work .


  • Ahmed Ibrahim January 24, 2017, 6:46 pm

    Thank you Very Nice explanation

  • jkjk February 27, 2017, 11:47 pm


  • gyugu February 27, 2017, 11:48 pm


  • Anonymous February 27, 2017, 11:49 pm


  • zues cu March 16, 2017, 1:13 pm

    keep it up

  • Kasper May 1, 2017, 7:14 am

    Good article. But it would be nice if there were some diagram.. It helps to understand it better, when you can see it visual. At least for me 🙂

  • CM November 11, 2017, 2:59 pm


    I’ve used Hibernate from time to time, but never really understood it that well. Your tutorials are very clear and complete. Nice work.

  • Santhosh July 1, 2018, 11:29 am


  • dave boland August 13, 2019, 2:17 pm

    Re: Many-to-Many Relationship
    Thanks for the info – it is helpful. The problem is I don’t think it is complete. For example, if the PK’s are auto-number, then would not you have to write a query to get the numbers before the insert into the joint/joining/table? I would love to read a really detailed explanation of this subject as I am trying to do exactly this. There are a lot of SQL books, but none so far cover this topic in any detail.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.