A One-to-Many relationship occurs when an entity can have multiple instances of another entity in a relationship. The cardinality of the relationship between the two entities will dictate the number of those instances: 0, 1, or more. The entity on the many side must reference one and only one record in the parent entity.
You have to bring the primary key of the parent entity (the one side of a relationship) as a foreign key to the child entity (the many side of the relationship). If your primary key on the parent entity is composed of more than one attribute then the foreign key of the child entity should also have those attributes.
There are two ways of implementing One-to-Many relationships. They are as follows:
- By using Non-Identifying Foreign key.
- By using Identifying Foreign key.
Approach #1: Using Non-Identifying Foreign key
A non-identifying relation happens when the foreign key in the dependent entity is not part of its own primary key; this is the most common approach. In the example below we have a school/student relationship with the following details:
- Business Rules: Each school can have zero or more students.
Each student must attend one school only.
- Parent entity: SCHOOL
- Child or Dependent entity: STUDENT
- 1 to 1 side: SCHOOL
- many side: STUDENT
- Parent to child phrase: Each school can have 1 or more students
- Child to parent phrase: Each student must belong to a school
Approach #2: Using Identifying Foreign key
An identifying foreign key relation happens when the foreign key in the dependent entity is part of its own primary key. This approach is less popular, but perfectly valid. Let’s see in action:
- Business Rules: Each student can take many different tests during the school year.
Each test can only be taken once during a school year, and each test must belong to 1 student only.
- Parent entity: STUDENT
- Child or Dependent entity: TESTS
- 1 to 1 side: STUDENT
- many side: TESTS
- Parent to child phrase: Each student can take 1 or more students
- Child to parent phrase: Each test must belong to a student
As you see in this approach the FK is part of the primary key in the dependent entity. Also by using identifying relationship here we ensure that one particular test can only be taken once by a given student each school year. So if the student was allowed to take the same test more than once in a given school year we would have to follow the non identifying foreign key approach.
Note: The students/tests relationship could have been better implemented as a many-to-many relationship since many students can take the same test. This style was just done to illustrate the identifying foreign key concept.