数据库设计-项目和区域

Question

我有一个数据库结构设计问题。目标是为我们的客户提供每个地理区域的报价数据库。每个报价可在多个地区提供。

区域是分层的-例如:

subregion_1
  subregion_11
    region_111
    region_112
  subregion_12
    region_121
    region_122
subregion_2
  subregion_21
    region_221

现在我想在数据库中存储该报价的offer_1和区域。我将给你三个我必须实现的例子:

• 当我的offer_1存储在region_111中时，那么我想在用户浏览subregion_1, subregion_11和region_111时显示此报价
• 如果offer_1存储在区域subregion_11和region_121中，则当用户浏览subregion__1, subregion_11以及subregion_11, subregion_12和region_121的所有分支时，应显示报价
• 当我的offer_1存储在subregion_1时，报价显示在subregion_1页面和subregion_1下的所有分支。

我还必须提供一种方法来计算不同的报价在每个地区的动态和非常快的数量。有人对如何进行这种设计有什么建议吗?

这是我到目前为止写的。

Regions
------------------------------------------------------------
| id     | level1 | level2 | level3 | name         | level |
------------------------------------------------------------
| 02     | 02     | null   | null   | subregion_1  | 1     |
| 0201   | 02     | 01     | null   | subregion_11 | 2     |
| 020103 | 02     | 01     | 03     | region_111   | 3     |
------------------------------------------------------------
Offers to regions
------------------------
| offer_id | region_id |
------------------------
| 1        | 020103    |
| 1        | 0202      |
------------------------

我为连接level1, level2和level3的区域创建了id。在表Offers_to_regions中，我存储了报价和地区。在这里，我为报价1设置了级别3的区域(020103)和级别2的区域(0202)。在这个设计中，我遇到了如何查询每个区域不同报价的数量，以及如何查询level1, level2和level3区域上的区域报价的问题。

Answer 1

有一种很明显的方法就是使用id来指向父节点，就像这样

CREATE TABLE Regions (
    region_id INT AUTO_INCREMENT PRIMARY KEY,
    parent_id INT,
    region_name VARCHAR(100) NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES Regions(region_id)
);

但是在您的情况下，这可能被认为是一种反模式，因为通过层次结构进行查询不是那么容易(特别是如果级别的数量发生了变化)

另一种方法可以使用类似Path Enumeration的东西，在其中存储类似于unix路径的层次结构路径。例如

CREATE TABLE Regions (
    region_id INT AUTO_INCREMENT PRIMARY KEY,
    path VARCHAR(100),
    region_name VARCHAR(100) NOT NULL
);

这将允许你像这样存储你的层次结构

---------------------------------------------
| region_id | path       | region_name      |
---------------------------------------------
| 1         | 1/         | subregion_1      |
| 2         | 1/2/       | subregion_11     |
| 3         | 1/2/3/     | region_111       |
| 4         | 1/2/4/     | region_112       |
---------------------------------------------

这样，当查询您的报价表(其中每个报价将有一个参考到region_id)时，当浏览我们说subregion_1 (id为1)的报价时，您的查询可以看起来像这样。

select Offers.SOME_COLUMN, ......
from Offers, Regions
where Offers.region_id = Regions.region_id
and   Regions.path like '1/%'

还有其他模式可以对分层数据建模，例如嵌套集和闭包表(可能相关)，您可能也有兴趣研究一下。在选择/插入/删除性能方面，每个都有不同的优缺点

编辑:

我刚刚注意到你编辑了你的问题，而且报价可以属于多个地区。以上可能需要调整以支持分配多个区域，但基本思想仍然可以应用。