mysql - SQL 设计决策 : Should I merge these tables?

Question

我正在尝试为客户设计一个小型数据库。我的客户有一个与公立和私立学校合作的组织；对于所涉及的每所学校，每所学校都有一个实现(一章)。

为了设计这个，我把两个表放在一起；一个用于学校，一个用于分会。但是，我不确定是否应该将两者合并在一起。表格如下:

mysql> describe chapters;
+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| id                 | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| school_id          | int(10) unsigned | NO   | MUL |         |                |
| is_active          | tinyint(1)       | NO   |     | 1       |                |
| registration_date  | date             | YES  |     | NULL    |                |
| state_registration | varchar(10)      | YES  |     | NULL    |                |
| renewal_date       | date             | YES  |     | NULL    |                |
| population         | int(10) unsigned | YES  |     | NULL    |                |
+--------------------+------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

mysql> describe schools;
+----------------------+------------------------------------+------+-----+---------+----------------+
| Field                | Type                               | Null | Key | Default | Extra          |
+----------------------+------------------------------------+------+-----+---------+----------------+
| id                   | int(10) unsigned                   | NO   | PRI | NULL    | auto_increment |
| full_name            | varchar(255)                       | NO   | MUL |         |                |
| classification       | enum('high','middle','elementary') | NO   |     |         |                |
| address              | varchar(255)                       | NO   |     |         |                |
| city                 | varchar(40)                        | NO   |     |         |                |
| state                | char(2)                            | NO   |     |         |                |
| zip                  | int(5) unsigned                    | NO   |     |         |                |
| principal_first_name | varchar(20)                        | YES  |     | NULL    |                |
| principal_last_name  | varchar(20)                        | YES  |     | NULL    |                |
| principal_email      | varchar(20)                        | YES  |     | NULL    |                |
| website              | varchar(20)                        | YES  |     | NULL    |                |
| population           | int(10) unsigned                   | YES  |     | NULL    |                |
+----------------------+------------------------------------+------+-----+---------+----------------+
12 rows in set (0.01 sec)

(请注意，这些表不完整 - 我还没有实现外键。另外，请忽略某些字段的 varchar 大小，它们会发生变化。)

到目前为止，将它们分开的优点是:

1. 分别查询学校和章节更容易。我不知道是否目前是必要的，但是很高兴能够做到。
2. 我可以让一个章节不活跃在不直接影响学校信息。
3. 数据的一般分离 - 中的字段 “章节”直接相关 章节本身，而不是学校 它存在于其中。 (我喜欢 组织 - 这更有意义 大部头书。也遵循“只有关键”的口头禅。)
4. 如果可能的话，我们可以收集学校没有章节的数据与之相关，这可能使感觉如果我们最终想要人选择一所学校并自动填充数据。

缺点:

1. 学校和章节。据我所知，有只会是一对一两者之间的关系，所以这样做可能会引入更多可能导致错误的复杂性在线下(如导入数据来自电子表格，这是不幸的我会经常做的事情)。
2. 如果存在一对一的比例，并且ID 是 auto_increment 字段，我猜 chapter_id 和school_id 最终会相同 - 那么为什么不将它们放在一个表中呢？
3. 据我了解，这些章节在他们身上并不能真正识别拥有 - 他们被绑定(bind)到一所学校，并且因此应该是一个子集学校。他们真的应该表格中的单独对象？

现在，我倾向于将它们作为两个单独的表；似乎利大于弊，但我想确保我没有制造可能导致问题的情况。我一直在与我的客户保持联系，我正在尝试获取有关他们存储的数据以及他们想用这些数据做什么的更多详细信息，我认为这会很有帮助。但是，我想听听这里消息灵通的人的一些意见；有什么我没有想到的吗？这里的底线只是我想第一次就把事情做好。

Answer 1

我认为它们应该分开。但是，您可以使分会成为学校的子类型(而学校是父类(super class)型)并使用相同的 ID。在数据库的其他地方，您使用 SchoolID 的地方是指学校，而您使用 ChapterID 的地方是指章节。

CREATE TABLE School (
   SchoolID int unsigned NOT NULL AUTO_INCREMENT,
   CONSTRAINT PK_School PRIMARY KEY (SchoolID)
)

CREATE TABLE Chapter (
   ChapterID int unsigned NOT NULL,
      CONSTRAINT PK_Chapter PRIMARY KEY (ChapterID)
      CONSTRAINT FK_Chapter_School FOREIGN KEY (ChapterID) REFERENCES School (SchoolID)
)

现在，除非先有学校，否则您无法拥有章节。如果发生这种情况，您必须允许每所学校有多个章节，您将使用 ChapterID 重新创建 Chapter 表作为身份/自动递增，添加一个填充有相同值的 SchoolID 列，并将 FK 放在这个学校上，并且像以前一样继续，只将 ID 插入 SchoolID 而不是 ChapterID。如果 MySQL 支持向自动增量列插入显式值，那么提前将其设为 SchoolID 自动增量可以避免您以后遇到麻烦(除非支持将常规列切换为自动增量，在这种情况下没有问题)。

将它们分开的其他好处:

• 您可以直接与 SchoolID 或 ChapterID 建立外键关系，以便您存储的数据始终正确(例如，如果尚不存在章节，则在创建之前无法存储此类事物的相关数据) .
• 单独查询每个表会执行得更好，因为行不包含无关信息。
• 可以使用某些必需的列创建学校，但该章节未创建(暂时)。然后，当它被创建时，您也可以在其中包含一些 NOT NULL 列。