Learn How to Implement Many-To-Many Relationships With an Easy Example
Relational database systems, such as MySQL, PostgreSQL, or SQLite make storing and retrieving data quick and efficient. The underlying principle of a relational database is to store data in related tables, which avoids data redundancy. Tables can be related in several ways: in a one-to-one relationship, a one-to-many relationship, or in a many-to-many relationship.
Of these three, only the many-to-many relationship requires an extra table, called a join table, junction table, intersection table, or cross-reference table.
Let's figure out how to create many-to-many relationships in SQL.
- Learn How to Implement Many-To-Many Relationships With an Easy Example
- Understanding Many-to-Many Relationships
- Implementing a Many-to-Many Relationship in SQL
- Using Five to Create MySQL Databases
- Recap: How to Implement Many-to-Many Relationships in SQL
Understanding Many-to-Many Relationships
The real world is full of many-to-many relationships. Consequently, implementing this relationship in a database design is a common task for database administrators or anyone working with databases.
For example, let's say we have been given the task to build an Event Management System. The system is supposed to let us store data about events and participants:
- Events have an event name, location, and date, and
- Participants have names, email addresses, and a company.
This is a classic example of a many-to-many or N:N relationship: one event can have many participants. And one participant can attend many events. Our hypothetical participant John Doe, who likes to party, attended both Coachella and Burning Man. And so did his wife, Jane Doe. Each participant (John and Jane) attended many events. And each event had many participants.
How do we implement this in our database schema? Let's get started by thinking about our tables and entity relationship diagram.
Implementing a Many-to-Many Relationship in SQL
To implement a many-to-many relationship we need something called a join table.
A join table has exactly one purpose: it joins the two tables that have a many-to-many relationship. It stores all of the information describing the many-to-many relationship. In our case, it keeps track of every event and participant by referencing each event's and participant's primary keys.
So, let's begin and create our database.
Step 1: Creating the Event and Participant Tables
First, create your Event and Participant table. As described above, an event has a name, location, and date. A participant has a name, email, and company.
Here's what our tables look like:
What's the most important field in each one of these tables? It's the Primary Key, which in our database schema is marked by the little key icon. A primary key is a unique identifier of each record inside your table. In the Event table, the primary key is the EventKey. In the Participant table, it's the ParticipantKey.
The primary key is what we're referencing in our join table, which we will create in the next steps. And if you require, here's the SQL statement to create the two tables above:
-- Create a new database (if it doesn't exist)
CREATE DATABASE IF NOT EXISTS YourDatabaseName;
-- Use the newly created database
USE YourDatabaseName;
-- Create the Event table
CREATE TABLE IF NOT EXISTS Event (
EventKey INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
date DATE,
location VARCHAR(255)
);
-- Create the Participant table
CREATE TABLE IF NOT EXISTS Participant (
ParticipantKey INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
company VARCHAR(255),
email VARCHAR(255)
);
Step 2: Creating the EventParticipant Join Table
Now, let's create our join table. The join table has exactly three fields: its own primary key, and the EventKey and ParticipantKey as foreign keys from our Event and Participant table.
Here's what our database schema will look like with the join table established:
Note that our join table has the name EventParticipant: this is easy-to-understand and descriptive. Usually, join tables follow this naming convention of using the names of the two tables represented in it.
Secondly, our join table has a one-to-many relationship to both Participant and Event. This ensures that whenever a participant joins an event, a new record is created in our join table. In this way, the table keeps track of all events and their participants. A many-to-many relationship is effectively just two one-to-many relationships.
Lastly, also note that the join table does not store any redundant information. It only stores what it needs to know: the primary keys of participants and events. Now, if you would like to write a query such as "Which events did John Doe participate in?", you can
- identify John through his ParticipantKey in your Participant table,
- identify all events that John participated in from the EventParticipant table. This will enable you to get access to each event's EventKey, and
- get additional information about each event from the Event table using the EventKey.
To create this table and its relationships, use this SQL statement:
-- Create the Join Table to establish a many-to-many relationship
CREATE TABLE EventParticipant (
PRIMARY KEY (EventID, ParticipantID),
FOREIGN KEY (EventID) REFERENCES Event(EventKey),
FOREIGN KEY (ParticipantID) REFERENCES Participant(ParticipantKey)
);
You're already done: to implement your many-to-many relationship, you have created three tables that each store unique records: one in relation to your events, one in relation to your participants, and one that puts them all together by storing the Primary Keys of Participants and Events in a join table.
The logic behind setting up a many-to-many relationship in this way is pretty simple: we can now retrieve from our join table a record. Said record will guide us toward our participant table via the ParticipantKey. And it will also guide us toward our event table using the EventKey. In short, the join table gives us just the right amount of information to match participants with events and vice versa.
Using Five to Create MySQL Databases
Five is an easy-to-use database application builder that comes with a graphical interface for creating a MySQL database. For a free download, sign up here.
Five makes it easy to create a MySQL database because of its powerful, visual database features.
Visually Create Tables in Five
Five lets users create database tables visually and without writing SQL. For example, in order to create the Event table, users of Five can simply define their fields, data types, size, and default display type in a simple point-and-click Table Wizard.
Five's visual table wizard replaces the tedious task of having to write out everything in SQL. It replaces CREATE statements with an intuitive table designer that covers all common SQL data types.
Let Five Auto-Generate Primary Keys
Note how in the screenshot above, there are only three fields being added to my Event table. But where's our fourth field, the primary key?
Five will automatically add a primary key to a newly created table. You don't have to worry about doing so yourself. Given the importance of primary keys for data accuracy, this is a great time saver for developers.
Use Five to Create Table Relationships
Creating relationships between tables is also really easy in Five.
When creating a new table using the Table Wizard just mentioned, Five will ask you whether you'd like to establish any relationships between tables. Simply select the tables that you're new table is related to, and Five will insert a Foreign Key for you. This comes in very handy when creating a join table, which has two one-to-many relations!
Visually Inspect Your Database Schema
Your Entity-Relationship Diagram can be viewed in Five, using Five's visual database modeler. This handy feature gives you a visual representation of your database, its tables, and relationships. If you're ever in doubt about your database structure, Five's database modeler is a handy tool to figure out how different tables are related to each other. All of the screenshots shown above of the Event, Participant, and EventParticipant join table were created using Five's database modeler.
Overall, Five makes SQL easy and accessible: you don't need to struggle with queries, complicated setups or credentials. Simply download Five and get started!
Recap: How to Implement Many-to-Many Relationships in SQL
Here's our recap: many-to-many relationships are part and parcel of good relational database design. To implement a many-to-many relationship between two tables, create a new table containing the primary key of each table. Remember: many-to-many relationships are just two one-to-many relationships!
Top comments (2)
Thank you. Good article!
Thanks Luke!