CODEWITHSUNDEEP
databaseperformancehibernatedatabase-design
Anish
Anish

Reputation: 255

DB Design - Avoid the use of foreign key to hold child and parent relationship

I need to create a simple schema which would hold the relationship between a parent and child.

I have two approaches here

Approach 1

  1. Have a parent table
  2. Have a child table and add the parent id as a foreign key dependency to it.

Approach 2

  1. Have a parent table
  2. Have a child table
  3. )Have a mapping table which identifies the relationship between parent and child(i.e parent and child are independent tables)

I know this is a bit of a silly question.But, still would like to know which approach is more recommended in the longer run as per the industry standards if any(as per recommended db design approaches)

My ORM implementation would be Hibernate.

Upvotes: 1

Views: 587

Answers (2)

Walter Mitty
Walter Mitty

Reputation: 18940

It depends. First off, I take it that a child parent relationship is many to one.

So approach 1 will work. The relationship is constrained to be many to one because a foreign key is juxtaposed with a primary key, and the primary key is constrained to be unique.

There are two problems with approach 2: First, it adds complexity without adding power. Second, it doesn't enforce the many to one nature of the relationship. It's going to take an extra constraint or careful application code to prevent a many to many situation arising in the data.

There is, however, an unusual case where approach 2 is worth it. It's where there is a reasonable expectation that the relationship will become many to many in the near future. In this case, approach 2 will require almost no changes to the database and no changes to the existing data in order to accommodate the new requirement.

Upvotes: 3

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

These two approaches model different things, so this is not a matter of what is more "recommended", but what fits your requirements.

  • The approach 1 models one-to-many relationship.
  • The approach 2 models many-to-many relationship1.

1 And the table in the middle is commonly called "junction" or "link" table.

Upvotes: 2

Related Questions