Learn How to Implement One-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.
In this blog post, we will learn how to create one-to-many relationships in SQL. We will use Five, a SQL-baed development environment, to do so.
- Learn How to Implement One-To-Many Relationships With an Easy Example
-
Implementing a One-to-Many Relationship in SQL
- Recap: How to Implement One-to-Many Relationships in SQL
Understanding One-to-Many Relationships
The real world is full of one-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 a system that keeps track of countries and cities. The system is supposed to have two tables: one for storing information about countries, and one for storing information about cities:
- A Country has a country name and population, and
- A City has a city name, a city population, and a postcode.
This is a classic example of a one-to-many or 1:n relationship: one country has many cities. But cities can only be part of one country. One-to-many relationships can also apply to human resource systems (a company has many employees, but an employee is only part of one company), order management systems (a customer can place many orders, but each order is only associated with one customer), or libraries and books (one library has many books, but a book can only be part of one library).
How do we implement this in our database schema? Let's get started by thinking about our tables and entity relationship diagram.
Implementing a One-to-Many Relationship in SQL
To implement a one-to-many relationship we need to relate two tables to each other. We do so through Primary and Foreign Keys.
Create a Relational Database in SQL with Five
Download Five for free to create a relational database, write queries, and build a user interface, such as a CRUD app on top of it! Once you have created your database, you can even export your database from Five as an SQL dump!
Sign Up For a Free Download
Download Five Today and Start Developing
Follow the steps below to create a one-to-many relationship between two tables. Let's begin and create our database.
Step 1: Creating the Country and City Tables
First, create your Country and City table. As described above, a country has a country name and population. A city has a city name, city population and postcode.
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 Country table, the primary key is the CountryKey. In the City table, it's the CityKey.
The primary key is what we're using to create a one-to-many relationship between our tables.
But before we continue, 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 Country table
CREATE TABLE Country (
CountryKey INT PRIMARY KEY,
Name VARCHAR(255),
Population INT
);
-- Create the City table
CREATE TABLE City (
CityKey INT PRIMARY KEY,
Name VARCHAR(255),
CityPopulation INT,
Postcode VARCHAR(10)
);
Step 2: Relating the Two Tables to Each Other
So far, we have two tables without a relationship. Now, let's add the one-to-many relationship to our database. To do so, we must add the CountryKey as a foreign key into the City table.
By inserting the CountryKey as a foreign key into the city table, we associate each city with a country (remember: one table's primary key column is another table's foreign key column).
Here's what our database schema looks like once we have created the one-to-many relationship.
Note that now we have a relationship between these two tables, shown by the line between the two tables. The "crow's feet" at the right end of the line show the direction of the relationship between our two entities: one country can have many cities, but not vice versa.
The City table now includes a column that contains the CountryKey for each record. This enables us to perform queries that utilize the join between the tables. For example, without the relationship we would not have been able to figure out what percentage of a country's total population lives in a particular city. Now that the relationship is established we can do so without repeating any information.
The advantage of storing data in related tables now becomes obvious: a city doesn't need to "know" which country it belongs to or what that country's population is. It only needs to know the country's foreign key to retrieve this information from the Country table. This makes data storage, retrieval, and querying quick and efficient.
Here is the SQL to create the two tables with the relationship:
CREATE TABLE Country (
CountryKey INT PRIMARY KEY,
Name VARCHAR(255),
Population INT
);
CREATE TABLE City (
CityKey INT PRIMARY KEY,
Name VARCHAR(255),
CityPopulation INT,
Postcode VARCHAR(10),
CountryKey INT,
FOREIGN KEY (CountryKey) REFERENCES Country(CountryKey)
);
One-to-many relationships are a fundamental building block of relational databases and we have just learned how to use them in our database design.
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.
Creating the two tables to create a database with a one-to-many relationship can accomplished in Five with just a few clicks!
1. Visually Create Tables in Five
Five lets users create database tables visually and without writing SQL. Simply download Five, create a new app, and go to Data > Table Wizard.
Using Five's Table Wizard, creating the Country table, is as easy as adding fields and defining their data type. Five supports all standard SQL data types, from binary to boolean to string.
In short, 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.
2. Let Five Auto-Generate Primary Keys
Five automatically creates primary keys, so you don't have to worry about them. Given the importance of primary keys for database design, this is a great time saver for developers.
Primary keys generated by Five are GUIDs, and the chance of ever having two primary keys being the same is virtually zero.
3. 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, Five will ask you whether you'd like to establish any relationships between tables.
Simply select the tables that your new table is related to, and Five will insert a Foreign Key for you. This comes in very handy when creating relationships.
4. Visually Inspect Your Database Schema
Your Entity-Relationship Diagram can be viewed in Five, using Five's visual database modeler.
This feature gives you a visual representation of your database, its tables, and relationships. If you are 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 City and Country tables 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. Five is a simple database builder (and so much more).
To get started, download Five and follow our tutorials, such as this beginner-friendly Excel to web app tutorial!
Recap: How to Implement One-to-Many Relationships in SQL
Here's our recap: one-to-many relationships are part and parcel of good relational database design. To implement a one-to-many relationship between two tables, insert a column that holds a foreign key into the table that be described with the sentence "One of x can have many of y". In our case: one country can have many cities. The table storing your y's must contain the foreign key of x.
Top comments (0)