Join Table with 3 Foreign Keys?

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.
enordengAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.