我能进一步规范我的实验数据表吗？

Question

我正在尝试将一个MySQL数据库连接到3NF，但我不知道如何到达那里。我是数据库设计新手，所以如果我的理解是错误的，请纠正我。

我有6列：date(yyyy-mm-dd), compound_type, location, method, value, unit

该数据库表示在某一特定地点采集的样本的值。每个采集样本的日期事件有21个地点，每个地点有10个不同类型的化合物。每种化合物都有三种不同的运行方法。因此，在每一天的每个位置上，每个复合类型的每个方法都有一个值(和相关的单位列)。

通过设置这样的表，我得到了一个伪1NF：

id(PK), date,       compound_type,  location,   method, value,  unit   
1,      2011-07-03, 1,              1,          1,      foo,    g
2,      2011-07-03, 2,              1,          1,      bar,    g
3,      2011-07-03, 1,              2,          1,      789,    g
4,      2011-07-03, 2,              2,          1,      123,    g
5,      2011-07-03, 3,              2,          1,      345,    g

用于所有其他化合物、位置和方法等。显然，每个化合物、位置和方法组合都有大量的重复日期。在我看来，重复这么多似乎是多余的。

我的问题是:是否有可能进一步规范这个数据库，或者使用id(PK)列，就像上面提到的最好的方法一样，可以处理所有重复的日期？我最好使用索引还是唯一的键？是否最好为每一个日期设置一个表，将其细分为复合/位置/方法类别？

我是个新手，所以你能给我指点的任何资源都会很棒。

最后，我将为这个数据库编写一个前端，用于报告和绘图，因此我正在寻找一个数据库设计，它将允许我使用简短的SQL语句返回数据。

Answer 1

第二正规形式

如果我正确地理解了您，则{date、compound_type、location、method}的组合唯一地标识{值、单位}和所有四个都是为了标识唯一的示例(例如，{date、location、method}本身是不够的)。

我要写这篇文章，就好像我没有收到关于函数依赖的问题的答案一样，因为其他人可能对这两种可能性的解释感兴趣。

如果没有部分依赖项，则为

。

1)假设非素数属性{value，unit}不依赖于部分候选键{id}或{date、compound_type、location、method}，则表位于2NF中，因为正如维基百科所指出的，“表的每个非素属性要么依赖于候选键的整体，要么依赖于另一个非素数属性。”

(如果存在部分依赖项

)

2)一个或两个非素数属性{value，unit}仅依赖候选键{date、compound_type、location、method}的部分。您已经确认这是{compund}->{unit}的情况，所以您的表不在2NF中。

为了修复2NF的违规行为，我建议将{unit}移到复合表中，我猜该表最终看起来会是这样的：{id，name，unit}。在这里，候选键是{id}和{name}。由于没有复合候选键，表自动为2NF。它也是3NF，因为不存在传递依赖关系，即没有依赖于单元的属性。

第三范式

好的，剩下的示例表如下所示：{id、date、compund_type、location、method、value}。这两个候选键是{id}和{date、compund_type、location、method}，这使得{value}成为单个非素数属性。假设没有更多的2NF违规行为(不能使用{date、compund_type、location、method}的子集来唯一确定值)，我们可以检查表中是否有违反3NF的情况。

3NF声明每个非素属性(不属于候选键的属性)必须直接依赖于每个超键。由于我们只有一个非素属性{value}，所以表不可能违反3NF，因为{value}不依赖于非素属性，也没有依赖{value}的非素数属性。

为了简单起见，我将不再讨论BCNF。

代理键与自然密钥

至于你的其他问题：“使用id(PK)列像我上面提到的所有重复日期的最佳方式吗？”我也这么想。在语义上，代理键id是不必要的，但它确实有助于保持简单。我不确定MySQL在屏蔽层下是如何工作的，但是在其他DBMS中，具有非整数数据类型的复合主键可能导致不必要的开销，例如索引时。复合键的另一个问题是，查询它们会变得很烦人。

假设您需要添加关于每个样本被发送到哪个实验室的信息。一个示例可以发送到多个实验室，每个实验室可以接收多个样本，因此您可以创建一个表来连接这两个表。你愿意写这个吗？

SELECT *
FROM samples s
JOIN labs_samples ls ON 
    s.date = sl.date,
    s.compund_type = sl.compund_type,
    s.location = sl.location,
    s.method = sl.method

或者这个

SELECT *
FROM samples s
JOIN labs_samples ls ON s.id = ls.id

？