database - 反规范化并减少四个数据库表之间的多个关系中的冗余

Question

我正在帮助一位社会科学家分析他的研究结果。他记录了数百次采访和其他会议，并收集了相关文件。他在MS Access中将这项工作的数据输入到四个表中：

+---------+-----------+-----------------------------------------------------+
|  Table  |    Key    |                  Example contents                   |
+---------+-----------+-----------------------------------------------------+
| cases   | ID_Case   | Description, case data                              |
| persons | ID_Person | Name, contact data                                  |
| events  | ID_Event  | Date, time, location of interview, meeting, etc.    |
| files   | ID_File   | Path, filename, date of recordings, documents, etc. |
+---------+-----------+-----------------------------------------------------+

我们可以称之为“基本”表。
为了表示这些表之间的关系，他首先尝试了一组六个表，这些表的键链接到原来的四个表：

+--------------+---------------------+
|    Table     |         Key         |
+--------------+---------------------+
| CasePersons  | ID_Case, ID_Person  |
| CaseEvents   | ID_Case, ID_Event   |
| CaseFiles    | ID_Case, ID_File    |
| EventPersons | ID_Event, ID_Person |
| EventFiles   | ID_Event, ID_File   |
| FilePersons  | ID_File, ID_Person  |
+--------------+---------------------+

我们可以称之为“二进制关系”表。
示例场景（假想）
下面是一个虚构的场景来演示他的数据结构示例。这个场景涉及三个案例，七个人，三个事件和五个文件。
假设他对人们的爱情生活进行了两次采访。一次是采访了四个人：乔治、萨利、亨利和埃利奥特。第二次采访是两个人，约翰和利兹。第一次采访是录像，第二次只是录音。第一次采访的一部分是与莎莉和亨利单独录制的。虽然利兹在第二次采访时，她没有说话，录音中也没有。在第一次采访中，乔治把一封情书的复印件分享给莎莉，并扫描成pdf文件。最后，第三个事件是与一位同事的电话会议，这是关于技术的，没有讨论具体的案例。
下表显示了这个假想场景的数据。在每个表的名称旁边，我给出了数据库中实际表中的记录数。
四个基本表
表[案件]（386条记录）

+---------+---------------------------------+
| ID_Case |           Description           |
+---------+---------------------------------+
|       1 | A husband and wife are in love. |
|       2 | A man and woman are in love.    |
|       3 | A man has never been in love.   |
+---------+---------------------------------+

表[人]（1472条记录）

+-----------+-----------+----------+
| ID_Person | NameFirst | NameLast |
+-----------+-----------+----------+
|         1 | George    | Brown    |
|         2 | Sally     | White    |
|         3 | Henry     | Green    |
|         4 | John      | Baker    |
|         5 | Liz       | Jones    |
|         6 | Elliot    | Brooks   |
|         7 | Catherine | Drake    |
+-----------+-----------+----------+

表[事件]（526条记录）

+----------+------------+---------------+
| ID_Event |    Date    |   Location    |
+----------+------------+---------------+
|        1 | 2016 06 01 | 123 Main St.  |
|        2 | 2016 07 02 | 456 Block Rd. |
|        3 | 2016 08 03 | Phone         |
+----------+------------+---------------+

表[文件]（1748条记录）

+---------+---------------------------+------+
| ID_File |         Filename          | Type |
+---------+---------------------------+------+
|       1 | Brown, Brooks interview   | avi  |
|       2 | White, Green subinterview | avi  |
|       3 | Brown letter              | pdf  |
|       4 | Baker interview           | wav  |
|       5 | Drake meeting             | wav  |
+---------+---------------------------+------+

六个二进制关系表
在二进制关系表的列表中，我将人员、事件和文件的键替换为相关文本，使其更易于阅读。ms-access通过根据查询显示表字段来允许相同的行为。类似地，某些表中显示的角色是数字外键的文本显示，用于分隔允许角色的表。
表[案件处理人]（720条记录）

+---------+-----------+---------------+
| ID_Case | ID_Person |     Role      |
+---------+-----------+---------------+
|       1 | George    | Husband       |
|       1 | Sally     | Wife          |
|       1 | Henry     | Wife's friend |
|       2 | John      | Boyfriend     |
|       2 | Liz       | Girlfriend    |
|       3 | Elliot    | Individual    |
+---------+-----------+---------------+

表[案例事件]（299条记录）

+---------+------------+
| ID_Case |  ID_Event  |
+---------+------------+
|       1 | 2016 06 01 |
|       2 | 2016 07 02 |
|       3 | 2016 06 01 |
+---------+------------+

表[案例文件]（301条记录）

+---------+---------------------------+
| ID_Case |          ID_File          |
+---------+---------------------------+
|       1 | Brown, Brooks interview   |
|       1 | White, Green subinterview |
|       1 | Brown letter              |
|       2 | Baker interview           |
|       3 | Brown, Brooks interview   |
+---------+---------------------------+

表[事件人]（700条记录）

+------------+-----------+-------------+
|  ID_Event  | ID_Person |    Role     |
+------------+-----------+-------------+
| 2016 06 01 | George    | Interviewed |
| 2016 06 01 | Sally     | Interviewed |
| 2016 06 01 | Henry     | Interviewed |
| 2016 06 01 | Elliot    | Interviewed |
| 2016 07 02 | John      | Interviewed |
| 2016 07 02 | Liz       | Present     |
| 2016 08 03 | Catherine | Present     |
+------------+-----------+-------------+

表[事件文件]（1490条记录）

+------------+---------------------------+-----------+
|  ID_Event  |          ID_File          |   Role    |
+------------+---------------------------+-----------+
| 2016 06 01 | Brown, Brooks interview   | Recording |
| 2016 06 01 | White, Green subinterview | Recording |
| 2016 06 01 | Brown letter              | Received  |
| 2016 07 02 | Baker interview           | Recording |
| 2016 08 03 | Drake meeting             | Recording |
+------------+---------------------------+-----------+

表[文件人]（2392条记录）

+---------------------------+-----------+-------------+
|          ID_File          | ID_Person |    Role     |
+---------------------------+-----------+-------------+
| Brown, Brooks interview   | George    | Interviewed |
| Brown, Brooks interview   | Sally     | Interviewed |
| Brown, Brooks interview   | Henry     | Interviewed |
| Brown, Brooks interview   | Elliot    | Interviewed |
| Brown letter              | George    | Writer      |
| Brown letter              | Sally     | Subject     |
| White, Green subinterview | Sally     | Interviewed |
| White, Green subinterview | Henry     | Interviewed |
| Baker interview           | John      | Interviewed |
| Drake meeting             | Catherine | Present     |
+---------------------------+-----------+-------------+

问题是所有这些表中输入的数据都有很多冗余。在数百次的访谈中，很容易出错，要么输入错误的数据，要么忘记在所有六个二进制关系表中输入所有数据。一旦输入数据，就很难检查错误。此外，冗余使得在数据中很难找到有意义的模式。
单四通关系表
我一直试图帮助他找出如何以更易于管理的方式表示这些数据。我一直在探索的一个想法是，将六个二进制关系表组合成一个包含四个关键字段的表：id_case、id_person、id_event和id_file。然后，上述数据变为：
表[casepersoneventfiles]（？？？记录）

+---------+-----------+------------+---------------------------+-----------------+---------------+-------------+-------------+
| ID_Case | ID_Person |  ID_Event  |          ID_File          |     CP_Role     |    EP_Role    |   EF_Role   |   FP_Role   |
+---------+-----------+------------+---------------------------+-----------------+---------------+-------------+-------------+
|       1 | George    | 2016 06 01 | Brown, Brooks interview   | Husband         | Interviewed   | Recording   | Interviewed |
|       1 | Sally     | 2016 06 01 | Brown, Brooks interview   | Wife            | Interviewed   | * Recording | Interviewed |
|       1 | Henry     | 2016 06 01 | Brown, Brooks interview   | Wife's friend   | Interviewed   | * Recording | Interviewed |
|       1 | Sally     | 2016 06 01 | White, Green subinterview | * Wife          | * Interviewed | Recording   | Interviewed |
|       1 | Henry     | 2016 06 01 | White, Green subinterview | * Wife's friend | * Interviewed | * Recording | Interviewed |
|       1 | George    | 2016 06 01 | Brown letter              | * Husband       | * Interviewed | Received    | Writer      |
|       1 | Sally     | 2016 06 01 | Brown letter              | * Wife          | * Interviewed | * Received  | Subject     |
|       2 | John      | 2016 07 02 | Baker interview           | Boyfriend       | Interviewed   | Recording   | Interviewed |
|       2 | Liz       | 2016 07 02 | 0                         | Girlfriend      | Present       |             |             |
|       3 | Elliot    | 2016 06 01 | Brown, Brooks interview   | Individual      | Interviewed   | * Recording | Interviewed |
|       0 | Catherine | 2016 08 03 | Drake meeting             |                 | Present       | Recording   | Present     |
+---------+-----------+------------+---------------------------+-----------------+---------------+-------------+-------------+

显然，这是更清洁和更紧凑。数据行的数量已从六个表中的36个减少到一个表中的11个。数据元素（非空单元格）的数量减少较少，从100个减少到85个。相关数据非常接近，可以更容易避免错误，或者看到任何存在的错误。
在这个表中，使用零代替空，以便允许前四个字段形成主键，从而确保这四个字段的唯一性。例如，在字段名中，“cp_role”表示“case person role”，即案例中人员的角色。
减少冗余，但不消除冗余。在本例中，13个与其他数据元素冗余的数据元素用星号标记。这种冗余是对数据输入错误的恶意邀请。但是，在这个表中自动检查这些错误是相当简单的。
在这个阶段实现这个四向关系表的最大问题是将六个二进制表中的数据折叠到其中。很容易创建一个包含所有必需字段的表，然后将六个表的所有行追加到该表中，对于缺少的键，该表的值为零。这就形成了一个包含5902条记录的组合表。如果上述示例场景中的数字可以外推到实际数据中，那么记录的数量可以减少到1800条左右，这意味着可以删除4000多条记录！我目前正在研究组合表中的模式，寻找自动合并和删除多余行组的方法。进展很慢。
比创建这个新表花费的时间更糟糕的是，我不能完全确信结果将是数据的最佳表示。
问题
我在这种方法中遗漏了一些重要的东西吗？有没有更智能的方法来管理这些数据集合？数据库理论是否提供了更好的方法来表示这些关系？

Answer 1

您可以在任何数据库手册中找到如何构造数据库的方法。例如，我想到：

接下来我们需要一些数据输入表单。数据输入表单设计用于获取案例数据并将其排序到适当的数据库表中。所以数据输入者不需要知道数据库的结构，只需要输入实际的数据。很难找到有关为1到多和多到多表单制作数据输入表单的信息。这里有一个链接：
create form to add records in multiple tables
以下是我为此数据创建的数据输入表单和最终子表单：

1到m关系的1面是表单，m面是子表单。在这里，我将使用向导创建的文件/人员窗体拖到了也使用向导创建的文件窗体上。为了能够表示第二个1到m的关系，我们将表单上保存外键的文本框替换为组合框。这里id_person被替换为一个组合框，因此数据输入程序不必知道id_person的任何信息，只需选择persons名称即可。
结果是，数据输入程序不需要知道数据库中底层关系的任何键，只需输入case数据。