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

I'm fairly new to database design, but I understand the fundamentals. I'm creating a relational database and I'd like to do something similar to creating a reusable type or class. For example, let's say I have a Customer table and a Item table. Customer and Item are related by a standard 1-to-many relationship, so Item has a column called CustomerId.

我对数据库设计很熟悉，但我了解基本原理。我正在创建一个关系数据库，我想做一些类似于创建可重用类型或类的事情。例如，假设我有一个客户表和一个项目表。“客户”和“项目”之间的关系是标准的“一到多”关系，因此“项目”有一个名为“CustomerId”的列。

I'd also like to have multiple "notes" for each Customer and each Item. In an normal OOP model, I'd just create a Note class and create instances of that whenever I needed. Of course, a relational database is different. I was thinking about having a Note table, and I'd like a 1-to-many relationship between Customer and Note, as well as Item and Note. The problem then is that the Note table will have to have a column for each other table that wishes to use this "type". (see example below) 如何在关系数据库中建模自定义类型?

我还想为每个客户和每个项目提供多个“注释”。在一个正常的OOP模型中，我只要创建一个注释类并在需要时创建实例。当然，关系数据库是不同的。我想要一个记录表，我想要客户和记录之间的一到多的关系，以及项目和记录。问题是，Note表必须为每个希望使用这种“类型”的表提供一个列。(见下面的例子)

I also thought that instead, I could create an intermediate table between Note and Customer/Item (or others). This would allow me to avoid having extra columns in Note for each table referencing it, so note could remain unchanged as I add more tables that require notes. I'm thinking that this is the better solution. (see example) 如何在关系数据库中建模自定义类型?

我还认为，我可以在Note和Customer/Item(或其他)之间创建一个中间表。这样可以避免为引用它的每个表添加额外的列，因此在添加更多需要注释的表时，Note可以保持不变。我认为这是更好的解决办法。(见示例)

How is this sort of situation usually handled? Am I close to correct? I'd appreciate any advice on how to design my database to have the sort of functionality I've described above.

这种情况通常如何处理?我说得对吗?我想知道如何设计我的数据库，使其具有我上面描述的那种功能。

2 个解决方案

#1

Yes, your concluding example is correct, and should be the way to go.

是的，你的结论是正确的，应该是正确的。

You model a "complex type" in relational databases by creating tables. You can consider the table as a class: in fact ORM solutions often map a class directly to a table. An instance of the custom type is a row in its table, and the instance can be referenced by the value of the primary key.

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

You can use your custom "complex type" for fields in other tables by using the same data type as the primary key of the "complex type", and enforcing the relationship with a foreign key constraint:

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

Let's build a complex type for "countries":

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

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

And let's add a couple of "country" instances:

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

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

Now we're going to use our complex "countries" type in a "users" table:

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

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)
);

With the above model, we are guaranteed that the country field of the users table can only be a valid country, and nothing but a valid country.

通过上述模型，我们可以保证users表的country字段只能是一个有效的国家，而只能是一个有效的国家。

In addition, using a junction table, as you suggested, is also a suitable approach to deal with that kind of polymorphic relationship. You may be interested in checking out the following Stack Overflow posts for some further reading on this topic:

此外，正如您所建议的，使用连接表也是处理这种多态关系的合适方法。您可能有兴趣查看下面的堆栈溢出文章，以便进一步阅读这个主题:

How can you represent inheritance in a database?
如何在数据库中表示继承?
Possible to do a MySQL foreign key to one of two possible tables?
可以对两个可能的表中的一个执行MySQL外键吗?

#2

I think you can best add a Note field to the Customer table and the Item table. In this note field (foreign key) you can store the id of the nota that belongs the the Customer / Item. To make sure you can attach multiple notes to a Customer or Item you could choose to add a Notes table and attach single Notes to the "Notes" table that you can attach to your Customer / Item table.

我认为您可以最好地向Customer表和Item表添加一个Note字段。在这个说明字段(外键)中，您可以存储属于客户/项的nota的id。为了确保可以将多个注释附加到客户或项上，您可以选择添加一个notes表，并将单个注释附加到可以附加到客户/项表的“notes”表。

#1