sql - 项目和专业项目 : Multiple tables with duplicate columns, 主表和明细表，还是...？

Question

我需要存储与“项目”相关的数据，其中将有各种不同的项目类型，所有项目类型都有共同的属性，然后每种类型都有自己的附加属性。我希望这是一个普遍的要求；什么是最佳实践解决方案？我们正在使用 SQL Server。

让我们使用一个虚构的例子:

车辆已

价格

制作

型号

业主

(在我们的真实数据中，将有 10-15 个公共(public)列。)

汽车 是车辆加:

风格(轿车、运动等)

颜色

发动机尺寸

船 是车辆加:

位移

始发港

...等等。对于几种类型的东西。在我们的真实数据中，每个专门类型通常会添加 2-5 列；将有 5 种类型开始。随着时间的推移，我们将添加类型，但总共可能只有 3 或 4 个(如果有的话)。添加类型需要开发，因此它不像可以由最终用户随意添加的“标签”。我们假设添加类型将需要更改数据库和客户端层，可能还有中间层。那完全没问题。

我们将对所有项目(车辆，在上面的示例中)进行大量查询；我们很少关心特定项目类型(汽车、船)的细节。

我看到了四种存储这些数据的方法:

用于汽车、船只等的单独表格，具有重复的列。

一张 table 与 Vehicle数据，表为附加Car数据，以及附加表 Boat数据。

一个项目表，一个单独的项目属性表，每个附加属性都有一行。例如，详细信息的软模式。

一张具有通用列的表，仅由非 DB 代码赋予含义。

查看每个:

用于汽车、船只等的单独表格，具有重复的列。例如，大致:

CREATE TABLE [Cars] (
    [Id] IDENTITY PRIMARY KEY,
    [Price] DECIMAL (19, 4),
    [Make] NVARCHAR(200),
    [Model] NVARCHAR(200),
    [Owner] INT,
    [Id] INT PRIMARY KEY,
    [Style] NVARCHAR(200),
    [Color] NVARCHAR(200),
    [EngineSize] DECIMAL(19, 2)
)
CREATE TABLE [Boats] (
    [Id] IDENTITY PRIMARY KEY,
    [Price] DECIMAL (19, 4),
    [Make] NVARCHAR(200),
    [Model] NVARCHAR(200),
    [Owner] INT,
    [Id] INT PRIMARY KEY,
    [Displacement] DECIMAL(19, 4),
    [PortOfOrigin] NVARCHAR(200)
)

很简单，汽车进入 Cars和船进Boats .如果我们添加更多车辆类型，我们会添加一个表格。如果我们添加另一个公共(public)列，我们必须返回并将其添加到所有车辆表中。通常可以针对所有表的联合 View (注意 Id 列)对车辆进行报告。

一张 table 与 Vehicle数据，表为附加Car数据，以及附加表 Boat数据。例如，大致:

CREATE TABLE [Vehicles] (
    [Id] IDENTITY PRIMARY KEY,
    [Price] DECIMAL (19, 4),
    [Make] NVARCHAR(200),
    [Model] NVARCHAR(200),
    [Owner] INT,
    [Type] INT        -- A type ID, e.g. "Car" vs. "Boat"
)
CREATE TABLE [Cars] (
    [Id] INT PRIMARY KEY,
    [Style] NVARCHAR(200),
    [Color] NVARCHAR(200),
    [EngineSize] DECIMAL(19, 2)
)
CREATE TABLE [Boats] (
    [Id] INT PRIMARY KEY,
    [Displacement] DECIMAL(19, 4),
    [PortOfOrigin] NVARCHAR(200)
)

所以每辆车在 Vehicles 中都会有一排和 Cars 中的一个链接行.每艘船将在 Vehicles 中有一排和 Boats 中的一个链接行.如果我们添加更多车辆类型，我们会添加一个表格。一般情况下，可以仅针对 Vehicle 对车辆进行报告。 table 。当检索特定 Car 的详细信息时或 Boat ，我们使用连接。

一个项目表，一个单独的项目属性表，每个附加属性都有一行。例如，详细信息的软模式。例如，大致:

CREATE TABLE [Vehicles] (
    [Id] IDENTITY PRIMARY KEY,
    [Price] DECIMAL (19, 4),
    [Make] NVARCHAR(200),
    [Model] NVARCHAR(200),
    [Owner] INT,
    [Type] INT
)
CREATE TABLE [VehicleDetails] (
    [VehicleId] INT,
    [Name] NVARCHAR(200),
    [Value] NVARCHAR(MAX)
)

所以每辆车在 Vehicles 中排一排和 VehicleDetails 中的三行(“样式”、“颜色”和“引擎尺寸”各一个)。报告主要针对 Vehicle 进行。 table 。对细节的报告开始变得越来越困惑。软模式有其一席之地，主要围绕用户定义的数据，但我认为这在这里不是一个好的选择。

一张带有通用列的表仅由非 DB 代码赋予含义:

CREATE TABLE [Vehicles] (
    [Id] IDENTITY PRIMARY KEY,
    [Price] DECIMAL (19, 4),
    [Make] NVARCHAR(200),
    [Model] NVARCHAR(200),
    [Owner] INT,
    [Type] INT,
    [Detail01] NVARCHAR(MAX),
    [Detail02] NVARCHAR(MAX),
    [Detail03] NVARCHAR(MAX),
    [Detail04] NVARCHAR(MAX),
    [Detail05] NVARCHAR(MAX),
    [Detail06] NVARCHAR(MAX),
    [Detail07] NVARCHAR(MAX),
    [Detail08] NVARCHAR(MAX),
    [Detail09] NVARCHAR(MAX),
    [Detail10] NVARCHAR(MAX)
)

所以 Car 数据会将 Style 分配给 Detail01 ，色至Detail02和 EngineSize 为 Detail03 ;对于船，我们将位移放在 Detail01和港口在 Detail02 .类似地，最终用户定义的模式可能有这个地方，但我猜当您可以控制数据库结构时，这不是一个好的答案。

Answer 1

这取决于。

方法 1 最适合大多数属性对大多数类型通用的情况。

方法 2 最适合大多数类型通用的属性很少的情况。

方法 3 本质上是方法 1，使用实体-属性-值方法来处理特定于类型的属性。这种方法最适用于大多数属性对于大多数类型都是通用的情况，并且很难预测需要哪些附加属性 - 在需要用户创建的字段的情况下非常常见。

方法 4 在任何情况下都不是一个好主意——它将语义内容从元数据层移除到代码层，同时保留了方法 1 的不灵活性。

还有另一种可能的方法 - 纯 Entity-Attribute-Value方法(基本上是方法 3 和方法 4 的混合)。由于在 RDBMS 上实现时产生的复杂性和较差的性能，这通常被认为是一种反模式。但是，在某些情况下，它是唯一可能的方法 - 主要是在事先不知道实体关系的情况下。