可变外键关系 [英] Variable Foreign Key Relationship

问题描述

我有一个普遍的高级问题。我正在尝试设计一个相当复杂的数据库，这种情况多次发生。



我有一个表，我用来存储修饰符（这需要是它自己的表，因为它包含修饰符的信息和描述，为什么它做它做的事情，等等。我遇到的问题是它修改的表会根据修饰符的变化而变化。有时它会引用表A，有时候是表B.



为了简单起见，这里有一个非常简化的ERD来说明问题：



< img src =http://i.imgur.com/198EZXE.jpgtitle =由imgur主办.com/>



这些表中没有任何一个都有明确的外键，它们只是分开的。



在学校里，他们只教我们数据库的第3种正常形式...我知道还有更多（我想到第5）。有人可以建议一种方法吗？除了在Modifier表中只有引用表的名称，并在应用程序端有一个相当大的case语句（不仅仅有2个引用表...更多大约10或20）。



感谢任何可以提供帮助的人！

I have a general, high-level question. I'm attempting to design a rather complicated database, and this happens multiple times.

I have a table that I'm using to store modifiers (this needs to be its own table, because it holds information and descriptions for what the modifier is, why it does what it does, et cetera). The problem I'm having, is that the table it modifies changes based on what the modifier is. Sometimes it will reference table A, sometimes table B.

To make things simpler, here's an extremely simplified ERD to illustrate the problem:

<img src="http://i.imgur.com/198EZXE.jpg" title="Hosted by imgur.com"/>

None of the tables have explicit foreign keys to each other, they're just separate.

In school, they only taught us up to 3rd normal form for databases...I know there's more (I think up to 5th). Can someone suggest a way to do this? Aside from just having the name of the references table in the Modifier table, and having a rather massive case statement on the application side (there are more than just 2 referencing tables...more around 10 or 20).

Thanks to anyone who can help!

推荐答案

你需要根据修改器类型将你的修饰符表分成其他几个表如上所述，这意味着你必须进一步规范化你的表，然后你的新表有自己的外键引用和相应的表。





看看这个例子：



我们有动物表它可以有一个带有肉类或蔬菜表的外键参考（基于动物吃肉或蔬菜），所以我们可以有两个不同的表，例如VegeterianAnimal，MeatEatingAnimal，每个表分别引用蔬菜或肉类表。这些新表与动物表有1-1关系，它具有这两个表的共同属性，如重量，高度等。

you need your modifiers table broken into some other tables based on modifier type you mentioned above, which means you have to normalize your table even further, then your new tables have their own foreign key references with corresponding tables.


look at this example:

we have a animal table which can has a foreign key reference with meat or vegetable table (based on animal eats meat or vegetable) so we can have two different tables such as , VegeterianAnimal, MeatEatingAnimal, each references Vegetable or Meat tables respectively. and these new tables have 1-1 relation with animal table, that holds common properties of these two tables, such as weight, Height etc.

这篇关于可变外键关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！