如何设计带有主键和多值属性的表？ [英] How to design table with primary key and multivalued attribute?

问题描述

我对数据库设计感兴趣，现在正在阅读相应的文献。
通过这本书，我面对一个奇怪的例子，使我感到不确定。
有一个关系

I'm interested in database design and now reading the corresponding literature. Through the book, i have faced a strange example that makes me feel uncertain. There is a relation

在这个表中，我们有一个复合主键（StudentID，Activity）。但ActivityFee部分依赖于表的键（Activity - > ActivityFee），所以作者建议将这个关系分成两个其他关系：

In this table we have a composite primary key (StudentID, Activity). But ActivityFee is partially dependent on the key of the table (Activity -> ActivityFee), so the author suggests to divide this relation into two other relations:

现在，如果我们来看看STUDENT_ACTIVITY，Activity变成外键，而关系仍然有一个复合主键。

Now if we take a look at the STUDENT_ACTIVITY, Activity becomes a foreign key and relation still has a composite primary key.

我们有一个表，其中整列定义了一个复合主键，是不是好吗？

We've got the table in which the whole columns defines a composite primary key, is it OK?

如果不是，我们该怎么办？ （可能定义一个代理键？）

If it is not, what should we do in this case? (probably define a surrogate key?)

为了消除可能的数据异常，什么是处理多值属性（我们的例子中的Activity）的好方法？

What is a good way to deal with multivalued attribute (Activity in our case) in order eliminate possible data anomalies?

推荐答案

复合候选键是没有问题的。 （如果你的引用不涉及候选键，即在任何其他情况下谈论主键，而不是刚刚只有一个候选键，得到一个新的引用。）

There is nothing wrong with a composite candidate key. (If your reference doesn't talk in terms of candidate keys, ie if it talks about primary keys in any other case than when there just happens to be only one candidate key, get a new reference.)

您的文本会告诉您什么是好的和坏的设计。没有必要担心每一个你注意到一个关系的属性可能是坏的。目前处理的好种类是正常化给出的。

Your text will tell you what is good and bad design. There is no point in worrying about every property you notice about a relation that it might be "bad". The kind of "good" it is currently addressing is that given by "normalization".

活动不是多值属性。 多值属性是非关系概念。该术语经常但不正确地用于表示非关系表中的属性，以某种方式（从未解释）具有每个行多于一个条目，或者在关系表中具有具有多个相似部分（集合，列表，包，表等）的值以某种方式（其从未解释）不适用于说，字符串&数字，或者对于具有多个不同部分（记录，元组等）的值的关系表中的列，以某种方式（从未解释）不适用于例如日期。 （有时它甚至被误用来表示一系列具有相似名称和值的属性，应该被一个属性替换为每个原始名称的行。）（这些只是不需要的设计的情况。）多值用作类似滥用/滥用术语原子的反义词。

"Activity" is not a "multivalued attribute". A "multivalued" attribute is a non-relational notion. The term is frequently but incorrectly used to mean either an "attribute" in a non-relational "table" that somehow (which is never explained) has more than one entry per "row", or for a column in a relational table that has a value with multiple similar parts (set, list, bag, table, etc) that somehow (which is never explained) doesn't apply to say, strings & numerals, or for a column in a relational table that has a value with multiple different parts (record, tuple, etc) that somehow (which is never explained) doesn't apply to, say, dates. (Sometimes it is even misapplied to mean a bunch of attributes with similar names and values, which ought to be replaced by a single attribute with a row for each original name.) (These are just cases of unwanted designs.) "Multivalued" gets used as an antonym of the similarly misused/abused term "atomic".

在列或表中出现不止一次的相同（值或）值的子行本身既不好也不坏。再次，您的参考将告诉您什么是好的设计。

Having the same (value or) subrow of values appear more than once in a column or table is, again, neither good or bad per se. Again, your reference will tell you what is good design.

这篇关于如何设计带有主键和多值属性的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！