在MySQL中以两种不同的方式绑定3个表 [英] Bind 3 tables in 2 different cases in MySQL

问题描述

我没有找到解决办法，所以我想问你。

整个事情是我有3个表：城市，区，机构。

• 地区与城市有关系
  

现在当我想要注册一个机构的时候，我必须选择一个机构所属的地区，但是如果这个城市没有任何地区的话，我必须选择这个城市本身。所以，问题是，如何将这些表格绑定到给定的情况？

区和城市必须保持联系

解决方案

有2张表，机构和地区 b

允许Areas链接到它自己，即area_id，parent_area_id

这样你总是把一个Institution连接到area_id，然后内部逻辑可以确定这个地区是否被认为是一个地区或一个城市。

因此，你现在有

$ $ p $ （
id）UNSIGNED INT NOT NULL PK AI，
area_id UNSIGNED INT NOT NULL，
name VARCHAR NOT NULL
）

和

 区域（
 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'
）
  

areas.type字段是可选的，但如果您想把它们定义为这样，那么这可能是d的一种方式在数据库中（或者假设parent_area_id = 0，那么它是一个城市，否则它是一个分区）

这种方法在选择字段时，你所做的只是

  SELECT * 
从院校
内部加入区域
 ON areas.id =院校。 area_id 
  

您可以100％确定机构area_id链接的位置，去区或城市表，它肯定会去区域表，然后以相同的方式对待区和城市，并以前端可能解释为城市或地区的格式呈现信息。可选的，如果你真的想要更进一步的话，可以选择

$ p $ SELECT
$，
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区域AS a_child
ON a_child.id = i.area_id
LEFT JOIN区域AS a_parent
ON a_parent.id = a_child.parent_area_id

例如，即使该机构被绑定到城市内的特定地区，也将始终返回城市名称

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

这篇关于在MySQL中以两种不同的方式绑定3个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！