CODEWITHSUNDEEP
databasedatabase-designrelational-database
medunes
medunes

Reputation: 586

Multiple relationships to the same entity

I have two entities:

person(id,name)

city(id,latCoord,longCoord,name)

Each person should have a city of birth, a city of work and a home city.

What is the right way for modeling these relationships:

1- Add three foreign keys from person to city

2- Create relationship table that maps relationships as follows

person_city_rel_type(id,type)

(1,"birth")

(2,"work")

(3,"home")

person_city(person.id,city.id,person_city_rel_type.id)

Upvotes: 0

Views: 1402

Answers (1)

philipxy
philipxy

Reputation: 15118

There is nothing wrong with multiple FKs (foreign keys) to the same table. Just declare a FK whenever subrows of some columns should always appear as subrows of some other columns (if that isn't already a consequence of other declared FKs).

So the straightforward design is better:

person1(id,name, birthCity, workCity, homeCity)
city(id, latCoord, longCoord, name)

Another straighforward design is:

person(id, name)
city(id, latCoord, longCoord, name)
birth_city(id, cityId)
work_city(id, cityId)
home_city(id, cityId)

Here there is a constraint that you need to add that for every X select id fromX_city = select id from person, ie there are FKs both ways with person. But these tables with only the FK constraints referencing person would be natural if a person's info for each city is optional.

Whereas in your alternative design a database state is only valid if person, person_city and even person_city_rel_type have values satisfying a certain complex constraint. Ie that each of the ids in person, and only ids in person, appears in person_city with exactly the three type value ids and person_city_rel_type has exactly that value. Ie that a certain complex constraint is satisfied, including that certain selects from person_city_rel_type join person_city equal the X_citys and that as above they be projections of person1.

When you start seeing that constraints need to hold that are expressible in terms of simpler tables, it's time to consider the simpler tables instead.

PS Your second proposal could just as well have been for person_city1(person_id, city_id, type). It's true that this allows invalid type values whereas to get an invalid person_city with your design (assuming a person_city_rel_type_id FK) you have to screw up person_city_rel_type. But the schema doesn't actually stop you from doing that. Either design needs to constrain the type column to the three values. So using ids instead of strings doesn't solve that problem.

Upvotes: 1

Related Questions