Learning SQL - Design Tips - Resolving Many To Many

These tips are mostly applicable to all forms of SQL, some are specific to SQLite and versions of SQLite like RealSQL Databases and RealSQL Server databases.

Many To Many realtionships can cause new database designers a lot of problems, so here is a quick basic overview of how and why we resolve manay to many relationships.

Many To Many

We've covered some basic database design in the previous lessons, now we're going to look at a many to many relationship and why it's very wrong.

For the purpose of this lesson we're going to create a new table in our test database to store records of books borrowed from the Library by children.

We may start our database design with a Data Model like this:

Many To Many Relationship Data Model

So, we could create a table like this to store our Library Books and link them to our Children table.

create table QiSQL_Library_Books (
id integer primary key,
book_title text,
child_id integer not null constraint fk_lb_ch1 REFERENCES QiSQL_Children(id) ) ;

Notice the child_id column in this table has a Foreign Key constraint - more about this is the Advanced Foreign Keys lesson. With SQLite you do not NEED to add the constraint (and currently SQLite will not do anthing with it anyway!). The important thing to understand is that Child_Id LINKS to the 'parent' record which is QiSQL_Children.

Have you noticed the problem with the above table definition? We have MANY books, we have MANY Children- how do we know which Child has borrowed the Book?

Link Entities

We need to create a new Entity in our Data Model - A Link Entity. Link Entities are used to resolve many to many relationships by defining the occurrence of the link. This table will have two foreign keys - child_id and book_id, so we now which child borrowed which book, we will also have some occurence data, so we will store the date the book was borrowed and the date the book was returned. Link entities quite often have a time or date element.

The Data Model would look like this:

Many To Many Resoved Data Model

As you can see each occurrence of Book Borrowed is linked to ONE child and ONE Book, there can be MANY occurrences of a Book being borrowed and MANY occurrences of Children borrowing books - Good!

Let's recreate the Library Book table and create the new Link Entity.

drop table if exists QiSQL_Library_Books;

create table
QiSQL_Library_Books (
id integer primary key,
book_title text);

create table QiSQL_Child_Library_Link (
id integer primary key,
child_id integer not null constraint fk_lb_ch REFERENCES QiSQL_Children(id) ,
book_id integer not null constraint fk_lb_lb REFERENCES QiSQL_Library_Books(id),
date_borrowed date not null,
date_returned date );

Link Entities are very often used to identify WHEN something happened, as you can see with this table we have included dates for borrowed and returned. You can see that we have now created two Foreign Key sin the new Link table - one on QiSQL_Children and one on QiSQL_Library_Books.