So when we're drawing these data model diagrams,
which we haven't spent too much time with,
we always draw them in a way so that we capture the manyness or
the cardinality of each end of the relationship.
Now, so that's one-to-many.
But sometimes we have a relationship between data that's many-to-many, and
frankly in our music data that we've been playing with, I already told you that, like,
well there's actually many artists on an album.
But I said don't worry about that. Well now we're going to worry about that.
So the example that I took from Wikipedia is books and authors.
There are many authors and many books.
And so, one author may talk to many books,
and one book may have different authors that they're connected to. And so
we can't say that either of these is a one relationship, and so
we call this a many-to-many.
On both ends there's a many-to-many.
But in terms of the table that we want to do, there is no way.
You can't put a single foreign key in this side.
Oops, come back.
We can't put a foreign key in this table to point to Books.
And we can't put a foreign key in Books pointing to Authors because that would be
sort of a one on either side.
And so we build a table in the middle, okay?
And in Wikipedia they call it a junction table, all kinds of names for
this thing, but what we do is we have a little table that we create.
We still have a Books table and a Authors table, but
then we have a table that models nothing but the connection between them and so
we decompose this many-to-many relationship into a
many-to-one relationship and a many-to-one relationship and these two things
then we can model properly using the exact same techniques that we've been using
all along.
And so we end up with a table that has two foreign keys in it and no primary key.
So let's take a look at how this looks when we actually build it and model it.
So here we're going to have something I care a lot about, courses and users.
So this has to do with the fact that one user is a member of many courses and
each course has many users so the relationship between courses and users,
the member-of relationship, is many on both ends.
And we just can't model that directly.
So we make a little mini-table in the middle, right?
So we call this the membership table or the table called Member.
We still have a User table and a Course table.
And we have a primary key in the Course and the User table.
And what we do is we have this Member table that has each row
has two foreign keys.
And it is a connection, so if you think of all the courses on one side, and
all the users on another side,
each connection, these could be friends of people,
each connection has one row
that connects a particular course with a particular user.
And that's how it works.
And so then that decomposes our many-to-many relationship
into two halves of two many-to-ones,
that then models the many-to-many relationship between the two tables.
We don't put an id.
We don't put that in there, because we can make a composite key,
which is, both these two things are the primary key for that table.
They're both numbers, duplication is allowed and so
this is all perfectly great.
Now sometimes we'll model something like put a role here so that you can say okay,
this is courses and users.
We might indicate that this particular user/course combination also
is an instructor and this particular is a student, right?
And so we can sometimes put a little bit of extra data on the link itself, and so
we have a little bit of data that we add down there.
Okay?
So we make this little table in the middle, and so here we're going to run
some databases. I'm going to start with a fresh database. I'm going to make a User
table, the main difference is I'm going to give the primary key for the users,
I'm going to make a Course table, and this is our standard primary key stuff.
I'm going to have a name and email for the users, and a title for the courses.
And then, those are just normal things that we're used to doing.
But now what we're going to do is create this little connector table
that's going to have two, it's going to have a role_id, and course_id.
I mean a user_id and a course_id.
And then we're going to model a little bit of data, right here in the middle, and
we're going to model role.
And this is going to allow us to say whether someone's a teacher or a student.
And the other thing that we're doing that's effectively a substitute for this
primary key is we're saying our primary key is actually two columns together.
And it's the combination.
So this forces it to be unique.
