A beginner’s guide to database table relationships

(Last Updated On: April 23, 2019)

Introduction

In a relational database, a relationship is formed by correlating rows belonging to different tables. A table relationship is established when a child table defines a Foreign Key column that references the Primary Key column of its parent table.

Every database table relationship is, therefore, built on top of Foreign Key columns, and there can be three table relationship types:

• one-to-many is the most common relationship, and it associates a row from a parent table to multiple rows in a child table.
• one-to-one requires the child table Primary Key to be associated via a Foreign Key with the parent table Primary Key column.
• many-to-many requires a link table containing two Foreign Key columns that reference the two different parent tables.

In this article, we are going to describe all these three table relationships as well as their use cases.

One-To-Many

The one-to-many table relationship looks as follows:

In a relational database system, a one-to-many table relationship links two tables based on a Foreign Key column in the child which references the Primary Key of the parent table row.

In the table diagram above, the post_id column in the post_comment table has a Foreign Key relationship with the post table id Primary Key column:

ALTER TABLE 
    post_comment 
ADD CONSTRAINT 
    fk_post_comment_post_id 
FOREIGN KEY (post_id) REFERENCES post

If you want to know which is the best way to map the one-to-many table relationship with JPA and Hibernate, then check outthis article.

One-To-One

The one-to-one table relationship looks as follows:

In a relational database system, a one-to-one table relationship links two tables based on a Primary Key column in the child which is also a Foreign Key referencing the Primary Key of the parent table row.

Therefore, we can say that the child table shares the Primary Key with the parent table.

In the table diagram above, the id column in the post_details table has also a Foreign Key relationship with the post table id Primary Key column:

ALTER TABLE 
    post_details 
ADD CONSTRAINT 
    fk_post_details_id 
FOREIGN KEY (id) REFERENCES post

If you want to know which is the best way to map the one-to-one table relationship with JPA and Hibernate, then check outthis article.

Many-To-Many

The many-to-many table relationship looks as follows:

In a relational database system, a many-to-many table relationship links two parent tables via a child table which contains two Foreign Key columns referencing the Primary Key columns of the two parent tables.

In the table diagram above, the post_id column in the post_tag table has also a Foreign Key relationship with the post table id Primary Key column:

ALTER TABLE 
    post_tag
ADD CONSTRAINT 
    fk_post_tag_post_id
FOREIGN KEY (post_id) REFERENCES post

And, the tag_id column in the post_tag table has a Foreign Key relationship with the tag table id Primary Key column:

ALTER TABLE 
    post_tag
ADD CONSTRAINT 
    fk_post_tag_tag_id
FOREIGN KEY (tag_id) REFERENCES tag

If you want to know which is the best way to map the many-to-many table relationship with JPA and Hibernate, then check outthis article.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Knowing the three types of table relationships is very important especially since, most often, the application developer uses several layers of abstractions when interacting with the database.

Also, when using an ORM tool, it’s very important to inspect the table relationships generated by the data access framework, to ensure that they match the standard definition and that they don’t try to emulate an association using a suboptimal approach.