One-to-many relationships are one of the most common database relationships. If you want to learn when and how to use one-to-many relationships, this article is a great starting point.
You’ll surely use one-to-many relationships to store information in any relational database, whether you’re designing enterprise-grade software or just creating a simple database to keep track of your uncle’s stamp collection.
A brief introduction to the relational model Relational
databases are a core component of any modern transactional application. The relational model consists of tables (data organized into rows and columns) that have at least one unique key that identifies each row. Each table represents an entity. This is shown in the following example, a very simple version of a table representing customer orders:
The diagram above, which I created online using Vertabelo, has only one table. Each row in the table represents an order, and each column (also known as an attribute) represents each individual piece of information contained in an order.
For those who are not yet familiar with the Vertabelo design tool, the article What are the symbols used in ER diagrams? explains the symbols and conventions used. You may also want to learn more about relational models and databases using our database modeling course.
What are relationships and why do we need them?
If we take a deeper look at the table used in the example above, we will see that it does not really represent a complete order. It doesn’t have all the information you would expect it to have. You will notice that it does not include any data related to the customer who placed the order, nor does it have anything about the products or services ordered.
What do we need to do to complete this design to store the order data? Should we add customer and product information to the Order table? That would require adding new columns (attributes) for customer names, tax identifiers, addresses, etc. as shown below:
“Order ID””Order Date””Order Amount”Customer”Customer Address””Customer Phone””Tax ID” 1Jun-23$10 248.15International Services Ltd1247 St River Blvd, Charlotte, NC(555) 478-8741IS789456 2Jun-27$14 785.45World Master Importing Inc.354 Mountain Hill Rd, Los Angeles, CA(555) 774-8888WM321456 3jul-01$7 975,00First State Provisioning Llc444 North Highway, Houston, TX(555) 698-7411FS947561 4jul-03$6 784,25International Services Ltd1247 St River Blvd, Charlotte, NC(555) 478-8741IS789456 5jul-07$21 476,10World Master Importing Inc.354 Mountain Hill Rd, Los Ángeles, CA(555) 774-8888WM321456 6jul-12$9 734,00First State Provisioning Llc444 North Highway, Houston, TX(555) 698-7411FS947561 7jul-17$14 747,45World Master Importing Inc.354 Mountain Hill Rd, Los Angeles, CA(555) 774-8888WM321456 8Jul-21$19 674,85International Services Ltd1247 St River Blvd, Charlotte, NC(555) 478-8741IS789456
If we do that, we will soon have problems. Most customers place more than one order, so this system will store customer information many times, once for each order from each customer. That doesn’t seem like a smart move.
Also, what happens when a customer changes their phone number? If someone needs to call the customer, they can find the old number on previous orders, unless someone updates hundreds (or even thousands) of existing orders with the new information. And the same would happen with any other change.
A relational model requires us to define each entity as a separate table and establish relationships between them. Storing all the information in a single table just doesn’t work.
There are several types of relationships between tables, but probably the most common is the one-to-many relationship, which is often written as 1:N. This type of relationship means that a row in one table (usually called the parent table) can have a relationship with many rows in another table (usually called a child table). Some common examples of one-to-many relationships are:
An automaker
- makes many different models, but a particular car model is built only by a
- make multiple purchases, but each purchase is made by a single customer
- A business may have many phone numbers, but one phone number belongs to one business.
single automaker. A customer can
.
There are also other types of relationships between tables; for more information about them, see this article on many-to-many relationships
.
Going back to our initial order example, the Customer table would be the main table and the Order table the child; a customer can have many orders, while an order can belong to a single customer.
Note that the one-to-many definition allows you to associate a row in the parent table with many rows in each child table, but does not require it. In reality, the design allows a customer to have zero orders (i.e. a new customer who has not yet made their first purchase), one order (a relatively new customer who has made a single purchase), or many orders (a repeat customer).
Let’s
take a look at a more complete example of a simple sales ordering system using an ER diagram (or entity relationship). (If you want to learn more about these diagrams, Vertabelo Features: Logical Diagrams is a great place to start.) Here’s the model:
This is a more realistic design. You will notice that there are new entities (tables) in the diagram, which now contains the Customer, Order, Order Detail, and Product tables. However, the most important thing you notice is that there are now relationships between the tables.
In a database model, relationships are represented by lines that connect two entities. The characteristics of these relationships are represented by different connectors:
- When there is a single vertical line, the entity closest to that connector has only one row affected by the relationship. It is the “one” in one to many.
- When there is a multi-line connector that looks like a crow’s foot, the entity closest to that connector has several rows affected by the relationship; they are the “many”.
Looking at the image and knowing the notation, it is easy to understand that the diagram defines that each Order can have many Order Details and that each Order Detail belongs to a single Order
. Implementing a One-to-Many Relationship Between Tables To define a one-to-many
relationship
between two tables, the child table must reference a row in the parent table. The steps required to define it are:
- Add a column to the child table that will store the value of the primary identifier. (Actually, most database engines allow it to be any unique key in the main table, not just the primary key.) The column can be defined as mandatory depending on the needs of your business; Even so, foreign key columns are usually made
Note: It is recommended that you keep the name of the reference columns the same as in the referenced table (parent). This makes it even easier to understand the relationship.
- Add a foreign key constraint on the child table. This indicates that each value stored in this new column refers to a row in the main table.
Foreign key constraints are a feature available in the relational database that enforces the following:
- When adding a row to the child table, the value in the reference column must match one value (and only one) in the parent table. (That’s why reference should be made to a column or set of columns that make up a primary or unique key.)
- or tries to modify the values of the single/primary key used as a reference and there is a child table referencing that row, the operation will fail.
If someone tries to delete a row from the parent table
These two features ensure that the database maintains its integrity. There is no ability to create orders that reference a non-existent customer, or to delete a customer who already has orders.
Creating a
Foreign
Key Syntax Foreign key syntax typically depends on the target database engine. Once you have defined your logical model, you can use the “Generate physical model…” in Vertabelo logic diagrams to transform the (database agnostic) model into a physical model that matches the database provider. Vertabelo will also generate the required SQL script that will allow you to create the tables and relationships in your target database.
Some practical examples of
1:N
relationships
Now let’s review some examples of
one-to-many relationships in the real world.
One-to-many relationship Using primary keys
This is probably the most common scenario when defining a one-to-many relationship. The child table uses the primary key value of the parent table to establish the relationship.
This example describes a basic online streaming service. Let’s review what is stored in each of the tables and how they relate to the other tables in our model:
- Each ServiceType defines how an account “behaves” (e.g. how many users can connect to the system at the same time, whether the account has Full HD enabled, etc.). It has a relationship with other entities:
- a one-to-many relationship with the account, which means that each type of service can have many accounts of that type.
- Each Account stores information about a customer. It has two direct relationships with other entities: Each account belongs to a
- single ServiceType, as explained above
- This table has a one-to-many relationship with the Profile table, which means that more than one user can connect to our system using the same account.
.
- Each profile represents a user in our system. It has two relationships with other entities:
- Each profile belongs to a single Account. This allows all family members (or perhaps a group of friends) to share the same account, while each has their own personal attributes (e.g. a profile name).
- Each profile has a unique Avatar.
- Each Avatar is an image that allows us to quickly identify each user of the account. You have a relationship with another entity: a
- one-to-many relationship with Profile, which means that a single avatar can be assigned to profiles on different accounts.
One-to-many relationships with natural or
surrogate unique keys
The use of surrogate primary keys is a widely accepted way of modeling tables. (Surrogate primary keys are generated by the database and have no real business value.) This method produces keys that are easier to use and adds some flexibility for when changes are required.
However, there are situations, for example, when we need to interact with external systems, in which using a key generated in our database is a bad approach. For those scenarios, it is generally best to use natural keys, which are unique values that are part of the entity being stored and are not automatically generated by our database.
The following example represents a basic data model of an organization that tracks vehicles (i.e., the car’s make, model, color, and year), their owners, and any associated traffic violations. When we define it, we use surrogate primary keys to establish the relationships between vehicles and makes, models and owners, since all this information is handled internally by our system.
In this system, how can a police officer in another city report an illegally parked car using our primary vehicle key (VehicleID)? Such information is not naturally available in the parked vehicle, but the license plate is there. That means the easiest way to receive and associate information from an outside source (in this example, any police department in the country) is by using a natural unique key instead of a substitute primary key.
The physical implementation of this logical diagram for SQL Server is available here:
One-to-many
relationships in the same table
Previous examples focused on relationships between two or more tables, but there are also scenarios where the relationship occurs between rows in the same table. This type of one-to-many relationship is also called a hierarchical relationship; It is used in many systems to represent tree-like structures, i.e. an organization chart, a general ledger account, or a product and its components.
The first time you need to create this type of structure, you’ll be tempted to define a table for each of the levels in your hierarchy, as shown in the following diagram
:
There are many problems with this approach:
- All tables are almost identical and store identical information.
- If your organization adds a new level, You will have to modify the data model and add a new table, new foreign keys, etc.
- If an employee receives a promotion, they must remove it from one table and insert it into another.
Therefore, the best way to model this type of structure is to use a single table that references itself, as shown in this diagram:
Here we see a single table of employees and a column called EmployeeID_Manager. That column refers to another employee in the same organization who is the supervisor/manager of the current employee.
I added the _Manager suffix to distinguish between the current row ID and the manager ID. (We could use ManagerID instead, but I prefer to keep the original name of the referenced column and, in those cases where both are in the same table, add a suffix explaining the role you actually have.)
Understanding hierarchical relationships is more complex than other one-to-many relationships. But if you forget about the table where all the information is stored and imagine that there are actually different tables, each of them representing a level in the hierarchy, it is a little easier to visualize. Imagine that you make the relationship between two entities and then combine them into a single entity.
What’s next?
The examples provided will help you identify different scenarios that require a one-to-many relationship. You can start designing your own database structure using Vertabelo Database Modeler, a web-based tool that allows you to not only generate a logical model, but also create a physical version of it for the database provider you need.
Now it’s your turn: use the comments section to tell us your thoughts on this article, ask additional questions, or share your database modeling experiences.
