A beginner’s guide to database table relationships
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
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.
A beginner’s guide to database table relationships@vlad_mihalceahttps://t.co/84cgiDkUhc pic.twitter.com/51qbRUlEQ6
— Java (@java) April 24, 2019
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 out this 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 out this 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 out this 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.
