Join Table with 3 Foreign Keys?

enordeng
enordeng used Ask the Experts™
on
Is it considered bad database design to have a join table with 3 foreign keys?

If there are doctors that practice a multiple locations.  And they have a different specialty at each location, can I have DoctorID, LocationID, SpecialtyID all in one join table as a comopsite key?  Multiple doctors could practice at the same location but have different specialties.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
No, this kind of design is okay, when you have properly normalized your tables and implemented all requirements.

But ternary relationships don't appear that much.

Without knowing your requirements, I would guess we don't have a ternary relationship here. Because normally not every speciality could be practiced at each location. Consider a radiologist.

So I think it is:

USE tempdb;

CREATE TABLE Doctors 
( 
	DoctorID INT NOT NULL, 
	DoctorName NVARCHAR(255) NOT NULL,
	CONSTRAINT PK_Doctors PRIMARY KEY ( DoctorID )
);

CREATE TABLE Locatations 
( 
	LocationID INT NOT NULL, 
	LocationName NVARCHAR(255) NOT NULL,
	CONSTRAINT PK_Locations PRIMARY KEY ( LocationID )
);

CREATE TABLE Specialities 
( 
	SpecialityID INT NOT NULL, 
	SpecialityName NVARCHAR(255) NOT NULL,
	CONSTRAINT PK_Specialities PRIMARY KEY ( SpecialityID )
);


-- Each doctor has studied n specialites.
CREATE TABLE PracticedSpecialities 
( 
	DoctorID INT NOT NULL, 
	SpecialityID  INT NOT NULL,
	CONSTRAINT PK_PracticedSpecialities PRIMARY KEY ( DoctorID, SpecialityID ),
	CONSTRAINT FK_PracticedSpecialities_DoctorID FOREIGN KEY ( DoctorID ) REFERENCES Doctors ( DoctorID ),
	CONSTRAINT FK_PracticedSpecialities_SpecialityID FOREIGN KEY ( SpecialityID ) REFERENCES Specialities ( SpecialityID )
);

-- Each speciality can be executed at only n defined places.
CREATE TABLE MedicalPractices
(
	LocationID INT NOT NULL,
	SpecialityID INT NOT NULL,
	CONSTRAINT PK_MedicalPractices PRIMARY KEY ( LocationID, SpecialityID ),
	CONSTRAINT FK_MedicalPractices_LocationID FOREIGN KEY ( LocationID ) REFERENCES Locatations ( LocationID ),
	CONSTRAINT FK_MedicalPractices_SpecialityID FOREIGN KEY ( SpecialityID ) REFERENCES Specialities ( SpecialityID )
);


CREATE TABLE Assignments
(
	DoctorID INT NOT NULL,
	LocationID INT NOT NULL,
	SpecialityID INT NOT NULL,
	CONSTRAINT PK_Assignments PRIMARY KEY ( DoctorID, LocationID, SpecialityID ),
	CONSTRAINT FK_Assignments_DoctorIDSpecialityID FOREIGN KEY ( DoctorID, SpecialityID ) REFERENCES PracticedSpecialities ( DoctorID, SpecialityID ),
	CONSTRAINT FK_Assignments_LocationIDSpecialityID FOREIGN KEY ( LocationID, SpecialityID ) REFERENCES MedicalPractices ( LocationID, SpecialityID ),	
);

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial