One-to-Many relationship in database

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.

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

1. 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
2. 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.

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!