数据库规范化：表设计异味
我的数据库的一部分的设计方式有三个表：
合作伙伴
- PartnerId
XYZPrograms
- XYZProgramId
PartnerXYZPrograms
- PartnerId
- XYZProgramId
每个合作伙伴可以有零个或一个 XYZProgram。 PartnerXYZPrograms 表将合作伙伴与 XYZProgram 相关联。因此，我在 PartnerXYZPrograms 表中有以下关系/约束：
PartnerId 和 XYZProgramId 是外键； PartnerId 是唯一的，XYZProgramId 也是唯一的。
现在这似乎闻起来了。六年后我又回到了数据库设计，所以我不能立即说这打破了规范化的规则，但我怀疑它打破了某些东西。 PartnerXYZPrograms 表很可能是多余的，XYZPrograms 表可能应包含 PartnerId。
所以我的问题是，在设计表明数据库规范化可能搞砸的表时，有什么气味。
The way a part of my database is designed I have three tables:
Partners
- PartnerId
XYZPrograms
- XYZProgramId
PartnerXYZPrograms
- PartnerId
- XYZProgramId
Each Partner can have zero or one XYZProgram. The PartnerXYZPrograms table relates the partner with a XYZProgram. So I have the following relationships/constraints in PartnerXYZPrograms table:
PartnerId and XYZProgramId are foreign keys; PartnerId is unique and also the XYZProgramId is unique.
Now this seems to smell. I am getting back to DB design after 6 years so I can't immediately say what rule of normalization this is breaking but I suspect it is breaking something. PartnerXYZPrograms table is most likely redundant and XYZPrograms table should probably contain the PartnerId.
So my question is what are the smells when designing tables that suggest database normalization is likely screwed up.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
发现这些气味的最简单方法是可视化数据集在活动时的外观。与规范化相关的明显且最基本的气味：
表的列中具有相同的冗余数据，其值不是另一个表的键（或复合键的一部分）。这应该引起警钟。如果您有一个名为“职位列表”的实体，并且其中一列“类别”包含“管理”、“管理”、“工程”、“管理”等值，那么这应该立即告诉您还有一个值得表示的附加概念。< /p>
表的某列中有冗余数据，该列有时填充的数据与之前的气味类似，有时为空。这看起来有点像上面的，但还是有区别的。这意味着有一个单独的想法需要表示，但它是组合关系的一部分。前任。名为 ManagerInfoID 的员工实体，具有指向经理表中包含一些信息的记录的键。对于所有非管理者来说它都是空的。 （我看到这个的次数超出了我的预期。
另一种可能的气味是合并表看起来不正确。通常情况下，人们会默认使用链接或合并表（如 ManagerEmployee）。虽然这实际上是处理多对多关系的最佳解决方案，但有时像您上面提到的复杂关系具有在合并表中看起来不正确的特征。更好（也许？）的解决方案是对零对多或一对多关系进行建模，类似于您上面提到的（通过将 PartnerId 放入 XYZPrograms 表中）。
老实说，我认为忘记标准化规则并坚持气味确实是一件好事。它可以防止像#3这样的事情发生，即从几年前你几乎不记得的课堂上混乱地想出规则通常会导致理论/实践不匹配，并让你错误地应用原则。
关系数据库的好处是，如果数据没有被正确建模，那么当你看到它时，它看起来真的很难看。这是由于受到非规范化数据库的影响（或更糟糕的是必须清理后果）。
你还能想到什么其他气味？
The easiest way to spot these smells is to visualize how the dataset will look when active. The obvious and most fundamental smells that relate to normalization:
A table has the same redundant data in a column, the values of which are not keys (or part of a composite key) to another table. This one should set off an alarm bell. If you have an entity called Job Listing and one of the columns "Category" contains values like "Management", "Management", "Engineering", "Management", this should immediately tell you that there is an additional concept worth representing.
A table has redundant data in a column that is sometimes populated similar to the previous smell and sometimes null. This seems a bit like the one above, but there is a distinction. This means that there is a separate idea that needs to be represented, but it is part of a composition relationship. Ex. Employee entity called ManagerInfoID that has a key to a record in the manager table with some info. It is null for all non managers. (I've seen this one more often than I would have liked.
Another possible smell is a Merge Table that just doesn't look right. More often than not, people gravitate a link or merge table (like ManagerEmployee) by default. While this is in fact an optimal solution to dealing with many to many relationships, sometimes complicated relationships like the one you noted above have characteristics that just don't seem right in a merge table. The better (perhaps?) solution is to model the zero to many or one to many relationship similar to what you have mentioned above (by putting the PartnerId in the XYZPrograms table).
In my honest opinion, I think that forgetting normalization rules and sticking to smells is a really good thing. It prevents things like #3 from happening, where a chaotic struggle to conjure up rules from a class you barely remember years ago usually results in a theory/practice mismatch and has you applying principles wrong.
The good thing about relational databases is that your data looks really ugly when you see it if it hasn't been modeled properly. This is something that comes from getting bit by de-normalized databases (or worse having to clean up the aftermath).
What other smells can you think of?