I'm working on a database design where a table is linked either to one or to another table yielding a total of 5 relationships. If the table is linked to table 1, the link to the 4 other tables is NULL. If I used 5 foreign keys, I will have a swiss cheese as a result!
I have table "center" which is either linked to district or canton, or department, or village, or city. If center belongs to district, it can no more belong to canton/department/village/city.
I cannot just have an attribut "center-type" which will show to what the center is linked because I also need to know to which instance of district, canton, etc. it's exactly linked.
Sincerely,
RLO
A similar question was asked and answered here: https://dba.stackexchange.com/questions/113122/conditional-foreign-key-relationship/114281#114281
What you can have is a Center table consisting of the data that is common to village, city and so forth. This includes the ID of the center and an indicator of which kind of center it is: City, Canton, Village, District or Department. Then a separate table for each kind of center with a PK that is also a FK back to the Center table.
So all entities that must have a relationship to a center has a FK to the Center table which tells you what kind of center it is and thus which other table holds additional information about the center.