Bind 3 tables in 2 different cases in MySQL

Question

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.

• Districts have a relationship with Cities

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

Answer 1

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

Answer 2

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.

Answer 3

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.