Database Relationships – One to Many

byTrevor PageonDecember 12, 2013

We’ve talked about relational databases already, and we’ve learned why this type of database management really dovetails with the object oriented programming model. So now I want to dive into the specifics when it comes to relationships.

What are the different types of relationships in SQL?

There are three types of relationships you can have in SQL, they are:

One-to-Many

One-to-One

Many-to-Many

In this episode we are going to be focusing on the One-to-Many relationship as it’s the most commonly used in my opinion. Let’s start off our talk by first exploring what a One-to-Many relationship looks like.

What does a One-to-Many relationship look like?

The typical example of a one to many relationship is when you’re talking about Users and Addresses. Typically a User can have one or more addresses (perhaps a mailing address and a billing address). The key here is that (in this particular design) any ONE Address can only belong to ONE User and ONLY ONE User.

This means that, for any particular Address that you could pick from the database table, that Address will only belong (or map to) exactly one User. This is what makes the relationship a One-to-Many relationship.

Other real world examples could include:

1 Employer has many Employees

1 Guitar has many Guitar Strings

1 Car has many Seats

There are countless different real world examples that can be thought up for this One-to-Many relationship, the key thing to understand is when to choose this relationship in your program.

When to choose a One-to-Many relationship

You’ll need to essentially ask yourself two questions to decide if you indeed want to implement a One-to-Many relationship:

Is there indeed a need for a “Many” side of the relationship? For example, what if your system only needs a User to have ONE Address? This would negate the use for a One-to-Many relationship, as any given User is required to input exactly one Address

Does the table (Object) on the “Many” side actually only map to 1 item in it’s related table? For example, what if you wanted to have any one particular address belong to multiple Users? Perhaps your system needs to keep track of which of its Users live at the SAME Address (like brother and sister, roommates or spouses). If this is the case, then you’d probably want to choose a Many-to-Many relationship

As you can see, there are many ways that you can implement a simple User -> Address relationship. You could make it a One-to-One, One-to-Many or Many-to-Many… it’s all in the design of your application and how YOU want it to function.

These are the kinds of things you’ll need to consider before committing to any particular table relationship.

How do you create a One-to-Many relationship in SQL?

Creating this One-to-Many relationship is all about your primary key <<PK>>. Let’s say we have chosen to use the One-to-Many relationship for our User -> Address mapping.

We know that we need to have a primary key <<PK>> for both tables. So our database tables may look something like this:

Users

<<PK>> user_id [int (11)]

username [varchar(20)]

password [varchar(20)]

Address

<<PK>> address_id [int (11)]

street_address_1 [varchar(255)]

street_address_2 [varchar(255)]

region [varchar(50)]

zip_code [varchar(7)]

country [varchar(50)]

Important Note: this hasn’t yet defined our One-to-Many relationship! We still need to make use of something called our Foreign Key <<FK>>.

To add our relationship to these tables, we’ll need to add in a foreign key <<FK>>. This foreign key is what is used to create a “link” between our tables. The typical way this is done, is to insert your foreign key into the table that represents the “Many” side of the One-to-Many relationship.

In this case, the “Many” side is the Address table. So we’ll need to add a link to our User table into the Address table. This can be done by inserting the primary key of the User table into the Address table.

Since we know that the primary key (by definition) will always only point to ONE unique row of data, this will be perfect for keeping track of which Address row is related to which User row.

Let’s put our User table’s primary key into the Address table:

Users

<<PK>> user_id [int (11)]

username [varchar(20)]

password [varchar(20)]

Address

<<PK>> address_id [int (11)]

<<FK>> user_id [int (11)]

street_address_1 [varchar(255)]

street_address_2 [varchar(255)]

region [varchar(50)]

zip_code [varchar(7)]

country [varchar(50)]

There we have it! We now have a design for our database tables that incorporates the One-to-Many relationship using a foreign key!

Please help to support these podcasts by filling out a quick survey. This will help keep this information FREE for you to consume and it will help me to keep creating these valuable tutorials at a predictable pace!

A one-to-many relationship is created using a foreign key in the child table which points back to the parent table’s primary key. The foreign key in the child table should NOT also be the primary key of the child table (otherwise you’d be creating a one-to-one).

While the attempt to teach is definitely laudable, my recommendation for anyone who visits this post seeking to learn about relational database design is to read material that has a solid theoretical foundation, for example IDEF1X (a standard) and, of course, A Relational Model of Data for Large Shared Data banks, by E.F. Codd.

Can I design database for @OneToMany relationship by inserting foreign key into Users table, and this foreign key will refer to primary key of Address table? In this way in Users table we will have repetitive row, excludind foreign key value which refers to the primary key of Address.