How To Create Many-to-Many Relationships in SQL
Tags: sql, sqlrelationships, tables, database
Published: 2023-11-20 21:05:00

<!-- wp:heading -->
<h2 class="wp-block-heading" id="learn-how-to-implement-many-to-many-relationships-with-an-easy-example">Learn How to Implement Many-To-Many Relationships With an Easy Example</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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<strong> related tables</strong>, 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 <strong>many-to-many relationship</strong>. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Of these three, only the<strong> many-to-many relationship requires an extra table</strong>, called a join table, junction table, intersection table, or&nbsp;cross-reference table. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Let's figure out how to create many-to-many relationships in SQL.</p>
<!-- /wp:paragraph -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:rank-math/toc-block {"headings":[{"key":"360f66c9-8dd5-4a7e-a24e-67a2d39ed7f6","content":"Learn How to Implement Many-To-Many Relationships With an Easy Example","level":2,"link":"#learn-how-to-implement-many-to-many-relationships-with-an-easy-example","disable":false,"isUpdated":false,"isGeneratedLink":true},{"key":"92f0cc79-5ba5-4b4f-a4c8-707694c205e6","content":"Understanding Many-to-Many Relationships","level":2,"link":"#understanding-many-to-many-relationships","disable":false,"isUpdated":false,"isGeneratedLink":true},{"key":"ca87b5af-db27-444c-9762-5353aec52a78","content":"Implementing a Many-to-Many Relationship in SQL","level":2,"link":"#implementing-a-many-to-many-relationship-in-sql","disable":false,"isUpdated":false,"isGeneratedLink":true},{"key":"c2ef321f-5a98-4d41-a4eb-9e25a0d36986","content":"Step 1: Creating the Event and Participant Tables","level":3,"link":"#step-1-creating-the-event-and-participant-tables","disable":false,"isUpdated":false,"isGeneratedLink":true},{"key":"af357f4a-41ee-443c-9501-4f6fca8f5b78","content":"Step 2: Creating the EventParticipant Join Table","level":3,"link":"#step-2-creating-the-event-participant-join-table","disable":false,"isUpdated":false,"isGeneratedLink":true},{"key":"7c94280d-5bfb-41f1-a659-50ef070aece7","content":"Using Five to Create MySQL Databases","level":2,"link":"#using-five-to-create-my-sql-databases","disable":false,"isUpdated":false,"isGeneratedLink":true},{"key":"22f21a1c-1914-4593-b9d7-be30a9be2b51","content":"Visually Create Tables in Five","level":3,"link":"#visually-create-tables-in-five","disable":false,"isUpdated":false,"isGeneratedLink":true},{"key":"0573dc7e-1773-441f-9727-71bf021fcdc2","content":"Let Five Auto-Generate Primary Keys","level":3,"link":"#let-five-auto-generate-primary-keys","disable":false,"isUpdated":false,"isGeneratedLink":true},{"key":"254163d6-078f-40a7-af21-480508a7edd1","content":"Use Five to Create Table Relationships","level":3,"link":"#use-five-to-create-table-relationships","disable":false,"isUpdated":false,"isGeneratedLink":true},{"key":"35b75325-963f-4014-8dd1-9bdc09ec8954","content":"Visually Inspect Your Database Schema ","level":3,"link":"#visually-inspect-your-database-schema","disable":false,"isUpdated":false,"isGeneratedLink":true},{"key":"12fffd35-d7f9-4238-91f9-07fb7beca676","content":"Recap: How to Implement Many-to-Many Relationships in SQL","level":2,"link":"#recap-how-to-implement-many-to-many-relationships-in-sql","disable":false,"isUpdated":false,"isGeneratedLink":true}],"listStyle":"ul"} -->
<div class="wp-block-rank-math-toc-block" id="rank-math-toc"><nav><ul><li class=""><a href="#learn-how-to-implement-many-to-many-relationships-with-an-easy-example">Learn How to Implement Many-To-Many Relationships With an Easy Example</a></li><li class=""><a href="#understanding-many-to-many-relationships">Understanding Many-to-Many Relationships</a></li><li class=""><a href="#implementing-a-many-to-many-relationship-in-sql">Implementing a Many-to-Many Relationship in SQL</a><ul><li class=""><a href="#step-1-creating-the-event-and-participant-tables">Step 1: Creating the Event and Participant Tables</a></li><li class=""><a href="#step-2-creating-the-event-participant-join-table">Step 2: Creating the EventParticipant Join Table</a></li></ul></li><li class=""><a href="#using-five-to-create-my-sql-databases">Using Five to Create MySQL Databases</a><ul><li class=""><a href="#visually-create-tables-in-five">Visually Create Tables in Five</a></li><li class=""><a href="#let-five-auto-generate-primary-keys">Let Five Auto-Generate Primary Keys</a></li><li class=""><a href="#use-five-to-create-table-relationships">Use Five to Create Table Relationships</a></li><li class=""><a href="#visually-inspect-your-database-schema">Visually Inspect Your Database Schema </a></li></ul></li><li class=""><a href="#recap-how-to-implement-many-to-many-relationships-in-sql">Recap: How to Implement Many-to-Many Relationships in SQL</a></li></ul></nav></div>
<!-- /wp:rank-math/toc-block -->


<!-- wp:heading -->
<h2 class="wp-block-heading" id="understanding-many-to-many-relationships">Understanding Many-to-Many Relationships</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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:</p>
<!-- /wp:paragraph -->

<!-- wp:list {"ordered":true} -->
<ol><!-- wp:list-item -->
<li><strong>Events</strong> have an event name, location, and date, and </li>
<!-- /wp:list-item -->

<!-- wp:list-item -->
<li><strong>Participants</strong> have names, email addresses, and a company.</li>
<!-- /wp:list-item --></ol>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>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. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>How do we implement this in our database schema? Let's get started by thinking about our tables and entity relationship diagram.</p>
<!-- /wp:paragraph -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:heading -->
<h2 class="wp-block-heading" id="implementing-a-many-to-many-relationship-in-sql">Implementing a Many-to-Many Relationship in SQL</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>To implement a many-to-many relationship we need something called a <strong>join table</strong>. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>So, let's begin and create our database.</p>
<!-- /wp:paragraph -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading" id="step-1-creating-the-event-and-participant-tables">Step 1: Creating the Event and Participant Tables</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Here's what our tables look like:</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":1829,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large"><img src="https://five.co/wp-content/uploads/2023/10/image-13-1024x494.png" alt="Five.Co - The Event and Participant Table" class="wp-image-1829"/></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>What's the most important field in each one of these tables? It's the <strong>Primary Key</strong>, 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.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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:</p>
<!-- /wp:paragraph -->

<!-- wp:kevinbatdorf/code-block-pro {"code":"\u002d\u002d Create a new database (if it doesn't exist)\nCREATE DATABASE IF NOT EXISTS YourDatabaseName;\n\n\u002d\u002d Use the newly created database\nUSE YourDatabaseName;\n\n\u002d\u002d Create the Event table\nCREATE TABLE IF NOT EXISTS Event (\n    EventKey INT PRIMARY KEY AUTO_INCREMENT,\n    name VARCHAR(255),\n    date DATE,\n    location VARCHAR(255)\n);\n\n\u002d\u002d Create the Participant table\nCREATE TABLE IF NOT EXISTS Participant (\n    ParticipantKey INT PRIMARY KEY AUTO_INCREMENT,\n    name VARCHAR(255),\n    company VARCHAR(255),\n    email VARCHAR(255)\n);\n","codeHTML":"\u003cpre class=\u0022shiki one-dark-pro\u0022 style=\u0022background-color: #282c34\u0022 tabindex=\u00220\u0022\u003e\u003ccode\u003e\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #7F848E; font-style: italic\u0022\u003e\u002d\u002d Create a new database (if it doesn\u0026#39;t exist)\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eCREATE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eDATABASE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #61AFEF\u0022\u003eIF\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eNOT\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eEXISTS\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e YourDatabaseName;\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #7F848E; font-style: italic\u0022\u003e\u002d\u002d Use the newly created database\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eUSE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e YourDatabaseName;\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #7F848E; font-style: italic\u0022\u003e\u002d\u002d Create the Event table\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eCREATE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eTABLE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #61AFEF\u0022\u003eIF\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eNOT\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eEXISTS\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eEvent\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e (\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    EventKey \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eINT\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003ePRIMARY KEY\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e AUTO_INCREMENT,\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003ename\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eVARCHAR\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e(\u003c/span\u003e\u003cspan style=\u0022color: #D19A66\u0022\u003e255\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e),\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003edate\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eDATE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e,\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003elocation\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eVARCHAR\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e(\u003c/span\u003e\u003cspan style=\u0022color: #D19A66\u0022\u003e255\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e)\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e);\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #7F848E; font-style: italic\u0022\u003e\u002d\u002d Create the Participant table\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eCREATE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eTABLE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #61AFEF\u0022\u003eIF\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eNOT\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eEXISTS\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e Participant (\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    ParticipantKey \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eINT\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003ePRIMARY KEY\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e AUTO_INCREMENT,\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003ename\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eVARCHAR\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e(\u003c/span\u003e\u003cspan style=\u0022color: #D19A66\u0022\u003e255\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e),\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    company \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eVARCHAR\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e(\u003c/span\u003e\u003cspan style=\u0022color: #D19A66\u0022\u003e255\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e),\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    email \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eVARCHAR\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e(\u003c/span\u003e\u003cspan style=\u0022color: #D19A66\u0022\u003e255\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e)\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e);\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003c/span\u003e\u003c/code\u003e\u003c/pre\u003e","language":"sql","theme":"one-dark-pro","bgColor":"#282c34","textColor":"#abb2bf","fontSize":".875rem","fontFamily":"Code-Pro-JetBrains-Mono","lineHeight":"1.25rem","headerType":"headlights","seeMoreString":"","seeMoreAfterLine":"","seeMoreTransition":false,"lineHighlightColor":"rgba(134, 167, 228, 0.2)","copyButton":true,"buttonTheme":"heroicons"} -->
<div class="wp-block-kevinbatdorf-code-block-pro" data-code-block-pro-font-family="Code-Pro-JetBrains-Mono" style="font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem"><span style="display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34"><svg xmlns="http://www.w3.org/2000/svg" width="54" height="14" viewBox="0 0 54 14"><g fill="none" fill-rule="evenodd" transform="translate(1 1)"><circle cx="6" cy="6" r="6" fill="#FF5F56" stroke="#E0443E" stroke-width=".5"></circle><circle cx="26" cy="6" r="6" fill="#FFBD2E" stroke="#DEA123" stroke-width=".5"></circle><circle cx="46" cy="6" r="6" fill="#27C93F" stroke="#1AAB29" stroke-width=".5"></circle></g></svg></span><span role="button" tabindex="0" data-code="-- 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)
);
" style="color:#abb2bf;display:none" aria-label="Copy" class="code-block-pro-copy-button"><svg xmlns="http://www.w3.org/2000/svg" style="width:24px;height:24px" fill="none" viewBox="0 0 24 24" stroke="currentColor" stroke-width="2"><path class="with-check" stroke-linecap="round" stroke-linejoin="round" d="M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4"></path><path class="without-check" stroke-linecap="round" stroke-linejoin="round" d="M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2"></path></svg></span><pre class="shiki one-dark-pro" style="background-color: #282c34" tabindex="0"><code><span class="line"><span style="color: #7F848E; font-style: italic">-- Create a new database (if it doesn&#39;t exist)</span></span>
<span class="line"><span style="color: #C678DD">CREATE</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">DATABASE</span><span style="color: #ABB2BF"> </span><span style="color: #61AFEF">IF</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">NOT</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">EXISTS</span><span style="color: #ABB2BF"> YourDatabaseName;</span></span>
<span class="line"></span>
<span class="line"><span style="color: #7F848E; font-style: italic">-- Use the newly created database</span></span>
<span class="line"><span style="color: #C678DD">USE</span><span style="color: #ABB2BF"> YourDatabaseName;</span></span>
<span class="line"></span>
<span class="line"><span style="color: #7F848E; font-style: italic">-- Create the Event table</span></span>
<span class="line"><span style="color: #C678DD">CREATE</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">TABLE</span><span style="color: #ABB2BF"> </span><span style="color: #61AFEF">IF</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">NOT</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">EXISTS</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">Event</span><span style="color: #ABB2BF"> (</span></span>
<span class="line"><span style="color: #ABB2BF">    EventKey </span><span style="color: #C678DD">INT</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">PRIMARY KEY</span><span style="color: #ABB2BF"> AUTO_INCREMENT,</span></span>
<span class="line"><span style="color: #ABB2BF">    </span><span style="color: #C678DD">name</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">VARCHAR</span><span style="color: #ABB2BF">(</span><span style="color: #D19A66">255</span><span style="color: #ABB2BF">),</span></span>
<span class="line"><span style="color: #ABB2BF">    </span><span style="color: #C678DD">date</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">DATE</span><span style="color: #ABB2BF">,</span></span>
<span class="line"><span style="color: #ABB2BF">    </span><span style="color: #C678DD">location</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">VARCHAR</span><span style="color: #ABB2BF">(</span><span style="color: #D19A66">255</span><span style="color: #ABB2BF">)</span></span>
<span class="line"><span style="color: #ABB2BF">);</span></span>
<span class="line"></span>
<span class="line"><span style="color: #7F848E; font-style: italic">-- Create the Participant table</span></span>
<span class="line"><span style="color: #C678DD">CREATE</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">TABLE</span><span style="color: #ABB2BF"> </span><span style="color: #61AFEF">IF</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">NOT</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">EXISTS</span><span style="color: #ABB2BF"> Participant (</span></span>
<span class="line"><span style="color: #ABB2BF">    ParticipantKey </span><span style="color: #C678DD">INT</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">PRIMARY KEY</span><span style="color: #ABB2BF"> AUTO_INCREMENT,</span></span>
<span class="line"><span style="color: #ABB2BF">    </span><span style="color: #C678DD">name</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">VARCHAR</span><span style="color: #ABB2BF">(</span><span style="color: #D19A66">255</span><span style="color: #ABB2BF">),</span></span>
<span class="line"><span style="color: #ABB2BF">    company </span><span style="color: #C678DD">VARCHAR</span><span style="color: #ABB2BF">(</span><span style="color: #D19A66">255</span><span style="color: #ABB2BF">),</span></span>
<span class="line"><span style="color: #ABB2BF">    email </span><span style="color: #C678DD">VARCHAR</span><span style="color: #ABB2BF">(</span><span style="color: #D19A66">255</span><span style="color: #ABB2BF">)</span></span>
<span class="line"><span style="color: #ABB2BF">);</span></span>
<span class="line"></span></code></pre></div>
<!-- /wp:kevinbatdorf/code-block-pro -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading" id="step-2-creating-the-event-participant-join-table">Step 2: Creating the EventParticipant Join Table</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Here's what our database schema will look like with the join table established:</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":1830,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large"><img src="https://five.co/wp-content/uploads/2023/10/image-14-1024x350.png" alt="Five.Co - For a many-to-many relationship a join table is required" class="wp-image-1830"/></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>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. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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 </p>
<!-- /wp:paragraph -->

<!-- wp:list {"ordered":true} -->
<ol><!-- wp:list-item -->
<li>identify John through his ParticipantKey in your Participant table, </li>
<!-- /wp:list-item -->

<!-- wp:list-item -->
<li>identify all events that John participated in from the EventParticipant table. This will enable you to get access to each event's EventKey, and</li>
<!-- /wp:list-item -->

<!-- wp:list-item -->
<li>get additional information about each event from the Event table using the EventKey.</li>
<!-- /wp:list-item --></ol>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>To create this table and its relationships, use this SQL statement:</p>
<!-- /wp:paragraph -->

<!-- wp:kevinbatdorf/code-block-pro {"code":"\u002d\u002d Create the Join Table to establish a many-to-many relationship\nCREATE TABLE EventParticipant (\n    PRIMARY KEY (EventID, ParticipantID),\n    FOREIGN KEY (EventID) REFERENCES Event(EventKey),\n    FOREIGN KEY (ParticipantID) REFERENCES Participant(ParticipantKey)\n);\n","codeHTML":"\u003cpre class=\u0022shiki one-dark-pro\u0022 style=\u0022background-color: #282c34\u0022 tabindex=\u00220\u0022\u003e\u003ccode\u003e\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #7F848E; font-style: italic\u0022\u003e\u002d\u002d Create the Join Table to establish a many-to-many relationship\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eCREATE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eTABLE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #61AFEF\u0022\u003eEventParticipant\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e (\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003ePRIMARY KEY\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e (EventID, ParticipantID),\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eFOREIGN KEY\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e (EventID) \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eREFERENCES\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eEvent\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e(EventKey),\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eFOREIGN KEY\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e (ParticipantID) \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eREFERENCES\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e Participant(ParticipantKey)\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e);\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003c/span\u003e\u003c/code\u003e\u003c/pre\u003e","language":"sql","theme":"one-dark-pro","bgColor":"#282c34","textColor":"#abb2bf","fontSize":".875rem","fontFamily":"Code-Pro-JetBrains-Mono","lineHeight":"1.25rem","headerType":"headlights","seeMoreString":"","seeMoreAfterLine":"","seeMoreTransition":false,"lineHighlightColor":"rgba(134, 167, 228, 0.2)","copyButton":true,"buttonTheme":"heroicons"} -->
<div class="wp-block-kevinbatdorf-code-block-pro" data-code-block-pro-font-family="Code-Pro-JetBrains-Mono" style="font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem"><span style="display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34"><svg xmlns="http://www.w3.org/2000/svg" width="54" height="14" viewBox="0 0 54 14"><g fill="none" fill-rule="evenodd" transform="translate(1 1)"><circle cx="6" cy="6" r="6" fill="#FF5F56" stroke="#E0443E" stroke-width=".5"></circle><circle cx="26" cy="6" r="6" fill="#FFBD2E" stroke="#DEA123" stroke-width=".5"></circle><circle cx="46" cy="6" r="6" fill="#27C93F" stroke="#1AAB29" stroke-width=".5"></circle></g></svg></span><span role="button" tabindex="0" data-code="-- 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)
);
" style="color:#abb2bf;display:none" aria-label="Copy" class="code-block-pro-copy-button"><svg xmlns="http://www.w3.org/2000/svg" style="width:24px;height:24px" fill="none" viewBox="0 0 24 24" stroke="currentColor" stroke-width="2"><path class="with-check" stroke-linecap="round" stroke-linejoin="round" d="M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4"></path><path class="without-check" stroke-linecap="round" stroke-linejoin="round" d="M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2"></path></svg></span><pre class="shiki one-dark-pro" style="background-color: #282c34" tabindex="0"><code><span class="line"><span style="color: #7F848E; font-style: italic">-- Create the Join Table to establish a many-to-many relationship</span></span>
<span class="line"><span style="color: #C678DD">CREATE</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">TABLE</span><span style="color: #ABB2BF"> </span><span style="color: #61AFEF">EventParticipant</span><span style="color: #ABB2BF"> (</span></span>
<span class="line"><span style="color: #ABB2BF">    </span><span style="color: #C678DD">PRIMARY KEY</span><span style="color: #ABB2BF"> (EventID, ParticipantID),</span></span>
<span class="line"><span style="color: #ABB2BF">    </span><span style="color: #C678DD">FOREIGN KEY</span><span style="color: #ABB2BF"> (EventID) </span><span style="color: #C678DD">REFERENCES</span><span style="color: #ABB2BF"> </span><span style="color: #C678DD">Event</span><span style="color: #ABB2BF">(EventKey),</span></span>
<span class="line"><span style="color: #ABB2BF">    </span><span style="color: #C678DD">FOREIGN KEY</span><span style="color: #ABB2BF"> (ParticipantID) </span><span style="color: #C678DD">REFERENCES</span><span style="color: #ABB2BF"> Participant(ParticipantKey)</span></span>
<span class="line"><span style="color: #ABB2BF">);</span></span>
<span class="line"></span></code></pre></div>
<!-- /wp:kevinbatdorf/code-block-pro -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:heading -->
<h2 class="wp-block-heading" id="using-five-to-create-my-sql-databases">Using Five to Create MySQL Databases</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Five is an easy-to-use database application builder that comes with a graphical interface for creating a MySQL database. For a free download, <a href="https://five.co/sign-up">sign up here</a>.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Five makes it easy to create a MySQL database because of its powerful, visual database features.</p>
<!-- /wp:paragraph -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading" id="visually-create-tables-in-five">Visually Create Tables in Five</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":1834,"width":840,"height":269,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large is-resized"><img src="https://five.co/wp-content/uploads/2023/10/image-15-1024x328.png" alt="Five lets users create database tables visually" class="wp-image-1834" width="840" height="269"/></figure>
<!-- /wp:image -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading" id="let-five-auto-generate-primary-keys">Let Five Auto-Generate Primary Keys</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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? </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading" id="use-five-to-create-table-relationships">Use Five to Create Table Relationships</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Creating relationships between tables is also really easy in Five.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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!</p>
<!-- /wp:paragraph -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading" id="visually-inspect-your-database-schema">Visually Inspect Your Database Schema </h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":1613,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large"><img src="https://five.co/wp-content/uploads/2023/08/Five.Co-Database-Modeler-1024x576.png" alt="Five.Co - Database Modeler for MySQL. The Modeler shows relationships between tables." class="wp-image-1613"/></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>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!</p>
<!-- /wp:paragraph -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:heading -->
<h2 class="wp-block-heading" id="recap-how-to-implement-many-to-many-relationships-in-sql">Recap: How to Implement Many-to-Many Relationships in SQL</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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!</p>
<!-- /wp:paragraph -->