mysql两个表绑定,在MySQL中以两种不同的方式绑定3个表

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

解决方案

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