sql-server - 将多个相同类型的表数据建模为一个表，以将所有表聚合为一个表

Question

我有一个关于数据建模的问题。假设我有下表 3 个学生表。 Source_table1 包含 A_ID 作为主键和 Name 作为属性。 Source_table2 将 B_ID 作为主键，将 Name & Address 作为其他属性。Source_table3 将 C_ID 作为主键，将 Name、Address 和 Age 作为属性。如果我们想创建一个新表作为 Student Master 并包含该表中的所有记录，我们该怎么做？如果我们要创建交叉引用表，那么我们应该如何处理该问题？

Answer 1

整合来自不同来源的数据很复杂。最后，您希望得到如下结果:

student (student_id PK, name, address, source1_id, source2_id, source3_id)

但是，要实现这一点，还有一些问题需要解决。

身份

您将如何识别不同来源中的匹配记录？看起来您的来源使用了代理标识符，但这些标识符在源数据库的上下文之外没有任何意义。您正在寻找的是合适的自然键。来源中唯一的共同点是学生的名字，但名字是众所周知的糟糕标识符。

实际测试数据而不是假设它会或不会起作用会很有用。例如，查询如下:

SELECT s1.name, COUNT(*) AS amount
FROM student_source_1 s1
INNER JOIN student_source_2 s2 ON s1.name = s2.name
GROUP BY s1.name
HAVING COUNT(*) > 1

重复 (student_source_2, student_source_3) 和 (student_source_1, student_source_3) 应该能让您对问题的规模有一些了解。

您可以根据姓名和地址匹配 student_source_2 和 student_source_3。如果两个来源对同一个学生有不同的地址(或其拼写)，这可能会产生更好的结果，或者更糟。这就引出了我们的第二个问题:

不一致

假设您可以解决身份问题，您可能需要处理不一致的数据。如果来源 2 和来源 3 对同一个学生有不同的地址怎么办？您如何确定正确的地址？

在某些情况下，仅映射源代码而不解决不一致就足够了。

在现实世界中即兴发挥

我在较难的情况下使用的一种技术是手动构建映射表，例如

student_map (student_id PK, source1_id, source2_id, source3_id)

每个 source_id 列都应该有一个唯一的约束，通常所有 3 个都可以为空。这是朝着上面的学生表迈出的第一步。

我将从插入所有完美的一对一匹配开始，然后将每个源与映射表左连接以获得不匹配的记录。将不匹配的源记录并排并排序，可以很容易地从视觉上发现可能的匹配项。这是一项繁琐且容易出错的工作，但有时无论如何都必须完成。对于不一致的地方，我可能会选择最完整/最好看的来源作为基础，并填补其他来源的空白。如果您能让老师或熟悉实际学生的人参与进来，或者向他们提供可供选择的备选方案，一定要这样做。

更多数据可能非常有用。如果来源有社会安全号码、家庭信息等，这些都可以用来匹配学生。在进行并排匹配之前，我会使用任意数量的查询来找到各种信息之间的完美匹配，并将它们插入到映射表中。

您很可能会发现由于设计不当而导致来源存在内部一致性问题 - 例如同一个学生的多个记录。这可能需要在继续之前修复源数据。

充分理解数据的关系模型对于此类工作非常重要，因为您将识别候选键、遵循依赖关系并遇到异常情况。