• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1701
  • Last Modified:

Intersection Tables

During the normalization process, when do you know there's a need for an intersection table?  At which stage (i.e., first, second, third normal form) does it become apparent there's a need for one?  What is the key sign that signals there's a need for one?
0
lcor
Asked:
lcor
  • 3
1 Solution
 
jdlambert1Commented:
Whenever you identify a many-to-many relationship between entities, it has to be resolved by putting an intersection/associate table between them.
0
 
jdlambert1Commented:
Many intersection table only have two columns, the primary key of each of the two tables. Of course, if the primary key of a table is comprised of more than one column, the intersection table has to have all the columns of the primary keys.

It can have additional columns as attributes, if the attribute is an attribute of the relationship, not of the entities the intersection table is related to.
0
 
jdlambert1Commented:
So, if your business logic dictates that one doctor can have many patients, and each patient can have many doctors, you could implement something like this:

Doctor(DoctorID, Title, FirstName, LastName,...)
[DoctorID is the primary key]

Patient(PatientID, FirstName, LastName,...)
[PatientID is the primary key]

DoctorPatient(DoctorID, PatientID, DateRelationshipBegan)
[Primary key is DoctorID and PatientID; DoctorID is a foreign key to Doctor; PatientID is a foreign key to Patient; and DateRelationshipBegan is a attribute of the relationship between doctors and patients.]
0
 
lcorAuthor Commented:
Answers my question perfectly!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now