数据库设计

Question

我正在创建一个数据库，记录多个表单的详细信息，每个表单都有一个状态和修订。我想创建一个表来保存所有表单的所有修订和状态数据，但是我对设计以及如何定义表单表与第一个修订和状态表之间的关系有点困惑。

我的表格如下。 为了简单起见，我缩写了表格

tasks
---------
pk int id


briefs
---------
pk int id

Revisions
---------
fk formId
int status

我想将任务和摘要表链接到修订表，但显然会与父表的 pk 发生冲突。是像查找表一样使用修订表并将修订表的主键存储在任务和摘要表中的最佳选择。

提前致谢

Answer 1

选项 1：

创建表单表，它是表单 ID 的中心来源。当插入到任务/简报/等中时（我假设可能还有更多），您首先插入到表单表中，然后使用该 ID 作为任务或简报中的 PK/FK。

然后你在Revisions中有FormID，只需引用Forms中的FormID即可。

您可能希望在 Forms 表中记录表单类型，并且可能会强制执行，如果您在 Forms 中插入了“1,Task”，则唯一可以插入 1 的表是 Tasks 表。有一些方法可以做到这一点。

您可能还想将其中一些事实隐藏在视图/触发器后面（因此您只需插入到任务中，然后在幕后插入到表单中，等等）。

选项 2：

在修订表中为每个表创建一列。添加一个约束，使这些列中的某一列不为空。这确实允许您使用更具体的外键约束（与选项 1 不同，在选项 1 中，仅仅因为 Forms 中有一个 Form ("1,Task")，您就不能保证 Tasks 表中会有一行）。但如果您要添加新的表单类型，则必须向修订添加更多列。

还有其他一些选择，但我想到的就是这两个。

Answer 2

如果任务和摘要中只有 id 而没有其他列，或者列相同，则可以在两个表中执行此操作。

tasks_and_briefs
----------
pk int id
int task_or_brief  (e.g 0 for Task, 1 for Brief) or varchar task_or_brief  (e.g. "Task", "Brief")  or whatever else you wish.

revisions
----------
pk int formId
int id
int status

Answer 3

听起来您可以将任务和摘要视为超类型“表单”的子类型。 （我在关系数据库设计意义上使用超类型和子类型，而不是在面向对象编程意义上。）

create table forms (
  form_id integer not null,
  form_type char(1) not null check (form_type in ('T', 'B')),
  other_form_columns char(1),
  primary key (form_id, form_type)
);

create table tasks (
  form_id integer not null,
  form_type char(1) not null default 'T' check (form_type = 'T'),
  other_task_columns char(1),
  primary key (form_id, form_type),
  foreign key (form_id, form_type) references forms (form_id, form_type)
);

create table briefs (
  form_id integer not null,
  form_type char(1) not null default 'B' check (form_type = 'B'),
  other_brief_columns char(1),
  primary key (form_id, form_type),
  foreign key (form_id, form_type) references forms (form_id, form_type)
);

完成此操作后，您可以创建一个修订表引用“forms”中的主键。

create table revisions (
  form_id integer not null,
  form_type char(1) not null,
  revision_num integer not null check (revision_num > 0),
  revision_status varchar(20) not null,
  other_revision_columns char(1),
  primary key (form_id, form_type, revision_num, revision_status),
  foreign key (form_id, form_type) references forms (form_id, form_type)
);

这种特殊的结构假设

• 您可以拥有多个状态
  修订号、
• 任务修订和摘要修订的意思是一样的。

如果任务修订与简要修订的含义不同，那么您需要一个用于任务修订的表，另一个用于简要修订的表。它们的主键不会引用超类型表“forms”；他们会引用子类型表“任务”和“简介”。