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?
Who is Participating?
jdlambert1Connect With a Mentor Commented:
Whenever you identify a many-to-many relationship between entities, it has to be resolved by putting an intersection/associate table between them.
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.
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.]
lcorAuthor Commented:
Answers my question perfectly!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.