如何在关系型数据库中建模自定义类型？

Question

我对数据库设计比较新，但我理解其基本原理。我正在创建一个关系型数据库，并且想要类似于创建可重用类型或类。例如，假设我有一个“客户”表和一个“项目”表。客户和项目之间通过标准的一对多关系相关联，因此项目具有名为“CustomerId”的列。

我还想为每个客户和每个项目创建多个“注释”。在普通OOP模型中，我只需创建一个“Note”类，并在需要时创建该类的实例。当然，关系型数据库是不同的。我考虑过创建一个“Note”表，并希望客户和注释以及项目和注释之间存在一对多的关系。然后问题就是，Note表将必须为希望使用此“类型”的所有其他表添加一列。(请参见下面的示例)

我也认为，相反地，我可以在Note和Customer / Item(或其他)之间创建一个中间表。这将允许我避免在参照它的每个表中为Note添加额外的列，因此Note可以随着我添加需要注释的更多表而保持不变。我认为这是更好的解决方案。(请参见下面的示例)

这种情况通常如何处理？我接近正确吗？我很感激任何关于如何设计我的数据库以具有上述功能的建议。

- Benny Jobigan

---

问题：一个注释是否可以应用于多个实体；即同一注释是否可以应用于客户和商品？或者同一注释是否可以应用于多个客户或多个商品？ - Adam Musch

不，每个笔记只针对一个客户、商品或其他事项。 - Benny Jobigan

你的数据库设计并没有阻止这样做。你应该将NOTE中的TEXT和DATE字段移动到CustomerNote和ItemNote中，以便正确映射每个注释最多可以与一个客户或商品相关联。因此，NOTE表将被完全消除。 - Adam Musch

我并不想删除Note表。这个思路的关键是将“note”这个概念推广成为可以反复使用的东西。因此，我的最终目标是在程序中创建一个单一的“笔记编辑器”控件，我可以将其重复使用在所有可能包含“笔记”的数据窗体上，例如客户编辑器窗体。 - Benny Jobigan

我不反对你想要的，我在指出设计的副作用--尤其是没有什么能够防止将同一个注释与多个客户、项目或任何组合关联起来。如果你一定要有一个统一的NOTE表，那就用检查约束实现第一个方案，强制各种CustomerID/ItemID列中只能有一个非空；虽然不够优雅，但是是正确的。如果你添加一个需要"notes"的新表，你还是必须进行数据库更改，以添加新的EntityNote表。 - Adam Musch

可能是如何在数据库中表示继承关系？的重复问题。 - philipxy

Answer 1

是的，你的结论性示例是正确的，也应该是前进的方式。

在关系数据库中，通过创建表来建模“复杂类型”。您可以将表视为类：实际上，ORM解决方案通常将类直接映射到表。自定义类型的实例是其表中的一行，并且可以通过主键的值引用该实例。

您可以使用与“复杂类型”的主键相同的数据类型，在其他表中的字段中使用自定义“复杂类型”，并使用外键约束强制执行关系：

让我们为“国家”构建一个复杂类型：

CREATE TABLE countries (
   iso_code     char(2)       NOT NULL,    
   name         varchar(100)  NOT NULL,
   population   bigint       
   PRIMARY KEY (iso_code)
);

让我们添加几个“国家”实例：

INSERT INTO countries VALUES ('IE', 'Republic of Ireland', 4470700);
INSERT INTO countries VALUES ('US', 'United States of America', 310403000);

现在我们将在“用户”表中使用我们复杂的“国家”类型：

CREATE TABLE users (
   id          int            NOT NULL,    -- primitive type
   name        varchar(50)    NOT NULL,    -- primitive type
   age         int,                        -- primitive type
   country     char(2),                    -- complex type
   PRIMARY KEY (id),
   FOREIGN KEY (country) REFERENCES countries (iso_code)
);

通过以上模型，我们可以确保users表的country字段只能是有效的国家，而且仅限于有效的国家。

此外，如你所建议的使用联结表也是处理多态关系的适当方法。你可能会对以下 Stack Overflow 的帖子感兴趣，以获取更多关于该主题的阅读资料：

• 如何在数据库中表示继承？
• 是否有可能将MySQL外键设置为两个可能的表之一？

- Daniel Vassallo

---

嗯，我想当我创建像“CustomerNote”这样的连接表时，我可以将其建模为ORM中的继承关系（我读到这是实现继承的多表方式 - 实际上，我已经设计了我的“Customer”表作为另一个“Contact”表的子类）。尽管我的目标不是为笔记创建继承，但本质上我正在为每个要从中引用“Notes”的表（例如客户和项目）创建笔记的子类（CustomerNote…）。当我在ORM中对此进行建模时，是创建单个“Note”类还是建模继承更好呢？ - Benny Jobigan

@Benny：这是一个有趣的问题，但我认为答案取决于ORM解决方案。不幸的是，我没有使用ORM的太多经验来提供更多的见解。 - Daniel Vassallo

Answer 2

我认为你应该在客户表和项目表中添加一个备注字段。在这个注释字段（外键）中，您可以存储属于客户/项目的注释ID。为了确保您可以将多个注释附加到客户或项目上，您可以选择添加一个注释表，并将单个注释附加到“注释”表中，然后将其附加到您的客户/项目表中。

- Younes