I did not found a solution to this, so I want to ask you.
The whole thing is that I have 3 tables: Cities, Districts, Institutions.
Now when I want to register an Institution, I have to choose a District from which the Institution belongs, but if that City doesn't have any district, I have to choose the City itself. So, the question is, how can I bind these tables for the given situation?
P.S. the Districts and Cities must remain bind
Have 2 tables, "Institutions" and "Areas"
Allow "Areas" to link to itself i.e. area_id, parent_area_id
This way you always link an Institution to an area_id, and then internal logic can deteremine whether that area is considered to be a District or a City.
So you now have
institutions (
id UNSIGNED INT NOT NULL PK AI,
area_id UNSIGNED INT NOT NULL,
name VARCHAR NOT NULL
)
and
areas (
id UNSIGNED INT NOT NULL PK AI,
parent_area_id UNSIGNED INT NOT NULL DEFAULT 0,
name VARCHAR NOT NULL,
type ENUM('city','district') NOT NULL DEFAULT 'city'
)
The areas.type field is optional but if you want to define them as such then that may be a way to do that within the database (else just assume that if parent_area_id = 0 then it's a city, else it's a district)
This way when selecting the field all you are doing is
SELECT *
FROM institutions
INNER JOIN areas
ON areas.id = institutions.area_id
You can be 100% certain where the institution area_id links to, there's no question mark over whether to go to the Districts or Cities table, it's definitely going to the areas table which in turn treats Districts and Cities in the same way and presents information in a format which your front end may then interpret as city or district. Optionally you could go a step further if you really wanted to
SELECT
i.*,
COALESCE(a_parent.id,a_child.id) AS city_id,
COALESCE(a_parent.name,a_child.name) AS city_name
FROM institutions AS i
INNER JOIN areas AS a_child
ON a_child.id = i.area_id
LEFT JOIN areas AS a_parent
ON a_parent.id = a_child.parent_area_id
That for example would always return the city name even if the institution was tied to a specific district within a city
There is a dirty, repeat dirty way of doing this: Have the institution have an ID field of type SIGNED INT, with negative numbers pointing to the ID of the City, and positive numbers pointing to the District. This does for easy joins on query.
Again: This is dirty, I recommend you avoid the situation alltogether (e.g. by creating a tree-table of cities and districts with a parent field), but if you can't change your model, this works.
This pattern is called polymorph associations, and it's not easy to solve nicely with SQL.
uid // common id table with globally unique ids
id int AUTO_INCREMENT
city
id UNIQUE FK:uid.id
district
id UNIQUE FK:uid.id
city_id FK:city.id
institution
location_id FK:uid.id
fruits
id UNIQUE FK:uid.id
FK means foreign key. You have to check manually that institution.location_id points to a district or city, not a fruit, or whatever else.
