CODEWITHSUNDEEP
database-designms-accessforeign-keysrelational-database
Chelle McGaughey
Chelle McGaughey

Reputation: 3

How to get around needing two foreign keys referencing same table

I've a Person table which needs to record data on the person's past and current occupation. I have an Occupation table with the list of occupation names and codes. Since within the Person table I can't have current_occupation as a foreign key referencing Occupation and also past_occuption as a foreign key referencing Occupation, how do I get around this? Even if I add a secondary table to record the relationship between Person and Occupation, I've still got the same problem of requiring two pieces of data from Occupation for each Person.

Upvotes: 0

Views: 1239

Answers (3)

ChrisPadgham
ChrisPadgham

Reputation: 870

SELECT Person.PersonId, Person.Name, 
CurrentOccupation.OccupationName AS CurrentOcc, 
PreviousOccupation.OccupationName AS PrevOcc
FROM (Person INNER JOIN Occupation AS CurrentOccupation ON Person.CurrentOccId = CurrentOccupation.OccupationId) 
INNER JOIN Occupation AS PreviousOccupation ON Person.PreviousOccId = PreviousOccupation.OccupationId;

This code has the occupation table twice in the SQL once using the foreign key for currentOcc and once using the foreign key for previousOcc however the suggestion above to create a new table called PersonOccupations is a more flexible and robust solution because it allows any number of occupations for the person.

Upvotes: 1

HansUp
HansUp

Reputation: 97101

Within the Person table you can have current_occupation as a foreign key referencing Occupation and also past_occuption as a foreign key referencing Occupation. In the Relationships window, add a second copy of the Occupation table. Then create a relationship line from current_occupation to one copy of Occupation, and another from past_occuption to the other copy.

However, this approach will limit you to only one past_occuption for each person. If you want to record multiple previous occupations for each person, the design Widor suggested will be better.

Upvotes: 1

Widor
Widor

Reputation: 13275

Why not have a PersonOccupation table with additional fields to index them by 'most recent' or simply the date from which they apply. Then derive the current and past_occupation from that?

Then you only need one Foreign Key relating the OccupationId between PersonOccupation and Occupation.

Upvotes: 0

Related Questions