主页 / dba / 问题 / 131910
Accepted
Human_AfterAll
Asked: 2016-03-11 18:07:56 +0800 CST

更好地在表上定义父子关系

  • 772

我们的团队正在开发一个新系统，它有一个 Warehouse 表，该表必须在条目之间具有层次关系，因为客户可以拥有 1 级、2 级、3 级和 N 级仓库。

我们首先认为这是最好的方法。通过在表上创建 Parent 和 Child 列，然后将 Parent 设置为 Child 的 FK（在同一张表上），但 DBA 朋友告诉我们有不同的方法。

因此，例如，我们曾考虑过制作这样的表格：

CREATE TABLE Warehouse(
filterId INT PRIMARY KEY IDENTITY,
parentId INT NOT NULL,
active BOOL DEFAULT 1,
warehouseName VARCHAR(30) NOT NULL,
FOREIGN KEY parentId REFERENCES Warehouse(filterId)

| FILTERID   | PARENTID | ACTIVE | WAREHOUSENAME |
--------------------------------------------------
|          1 |        0 |      1 | N WAREHOUSE 1 |
|          2 |        1 |      1 | ROOM 1        |
|          3 |        1 |      1 | ROOM 2        |
|          4 |        1 |      1 | ROOM 3        |
|          5 |        1 |      1 | ROOM 4        |
|          6 |        2 |      1 | SHELF 1       |
|          7 |        2 |      1 | SHELF 2       |
|          8 |        2 |      1 | SHELF 3       |
|          9 |        2 |      1 | SHELF 4       |
|         10 |        2 |      1 | SHELF 5       |
|         11 |        2 |      1 | SHELF 6       |
|         12 |        2 |      0 | SHELF 7       |
|         13 |        3 |      1 | BOX 1         |
|         14 |        3 |      0 | BOX 2         |
|         15 |        3 |      1 | BOX 3         |
|         16 |        3 |      1 | BOX 4         |
--------------------------------------------------

但是这个 DBA 说我们应该做这样的事情：

CREATE TABLE Warehouse(
filterId VARCHAR(20) PRIMARY KEY,
active BOOLEAN DEFAULT 1,
warehouseName VARCHAR(30) NOT NULL)

| FILTERID   | ACTIVE | WAREHOUSENAME |
---------------------------------------
| 1.00       |      1 | N WAREHOUSE 1 |
| 1.01       |      1 | ROOM 1        |
| 1.02       |      1 | ROOM 2        |
| 1.03       |      1 | ROOM 3        |
| 1.04       |      1 | ROOM 4        |
| 1.01.01    |      1 | SHELF 1       |
| 1.01.02    |      1 | SHELF 2       |
| 1.01.03    |      1 | SHELF 3       |
| 1.01.04    |      1 | SHELF 4       |
| 1.01.05    |      1 | SHELF 5       |
| 1.01.06    |      1 | SHELF 6       |
| 1.01.07    |      0 | SHELF 7       |
| 1.01.01.01 |      1 | BOX 1         |
| 1.01.01.02 |      0 | BOX 2         |
| 1.01.01.03 |      1 | BOX 3         |
| 1.01.01.04 |      1 | BOX 4         |
---------------------------------------

我们应该走哪条路？

一方面，我们在parentId和filterId之间建立了关系；另一方面我们没有任何关系，但很容易理解条目属于哪里。

并非所有的仓库都处于同一水平 - 这就是我所说的程度。它们的顺序应如下：对于客户 A，1 是 1.1 和 1.2 的父级，2 是 2.1 的父级，3 是 3.1 的父级，4 是 4.1 和 4.2 的父级 - 但 1、2、3 和 4 是相同的level（1st level），1.1, 1.2, 2.1, 3.1, 4.1 和 4.2 也是同一个level（2nd level），但不是同一个父级。

sql-server database-design

2 个回答

  1. Best Answer

    关系数据库中有几种分层数据模型。这些包括

    1. 邻接列表。这是你上面的建议。
    2. 路径枚举。这是 DBA 的建议。
    3. 嵌套集
    4. 封闭表

    每个都有不同的插入、删除、邻居读取和集合读取特性。您必须确定平均而言哪个最适合您的应用程序性能和开发需求。例如，嵌套集非常适合读取整个子树，但插入的成本很高，并且可能难以理解。邻接表易于理解和编码，读/写速度快但频繁，大型递归查询可能很昂贵。

    由于您使用的是 SQL Server，因此请注意hierarchyid数据类型。

    我发现 Joe Celko 的书“Smarties 中的树和层次结构”是最具可读性和信息量的。

    Stackoverflow上有一个讨论，它对此进行了广泛的讨论。

    • 3

  2. 在您的问题中，您询问“最佳性能”，但关于什么样的查询？目前尚不清楚哪个是典型查询或典型查询，所以我的回答将是通用的。

    我认为在这种情况下，最好的模式如下：

    Relation Customer, with attributes:
  idCustomer, integer primary key,
  other attributes...;

Relation Warehouse, with attributes:
  idWarehouse, integer primary key,
  idCustomer, integer, foreign key for Customer,
  idParentWarehouse, integer, foreign key for Warehouse,
  warehouseLevel, varchar(10),
  warehouseName, varchar(100),
  other attributes...
  constraint (idCustomer, warehouseLevel) unique;

    Warehouse 上的索引将位于主键和外键以及（idCustomer、warehouseLevel）上。

    其中，warehouseLevel 可以是 1、1.2、2.1.3 等字符串。

    使用这样的方案，您可以轻松找到：

    1) 一个客户的所有仓库，

    2）某个仓库的父仓库，

    3）某个仓库的所有子仓库，

    4）某个仓库的所有后代仓库（带递归查询）。

    此外，如果您还需要查找某个客户的某个程度的所有仓库，您应该在仓库中添加属性度，整数，索引为（idCustomer，度）。

    • 2

相关问题