For example, I have a table that stores classes, and a table that stores class_attributes. class_attributes has a class_attribute_id and a class_id, while classes has a class_id.
例如,我有一个存储类的表,以及一个存储class_attributes的表。 class_attributes有一个class_attribute_id和一个class_id,而classes有一个class_id。
I'd guess if a dataset is "a solely child of" or "belongs solely to" or "is solely owned by", then I need a FK to identify the parent. Without class_id in the class_attributes table I could never find out to which class this attribute belongs to.
我想如果一个数据集是“独生子女”或“仅属于”或“完全归属于”,那么我需要一个FK来识别父母。如果没有class_attributes表中的class_id,我永远无法知道该属性属于哪个类。
Maybe there's an helpful answer matrix for this?
也许这有一个有用的答案矩阵?
4 个解决方案
#1
1
Wikipedia is helpful.
*很有帮助。
In the context of relational databases, a foreign key is a referential constraint between two tables.1 The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table.
在关系数据库的上下文中,外键是两个表之间的引用约束.1外键标识一个(引用)表中的一列或一组列,这些列引用另一个列中的一列或一组列(引用)表。引用表中的列必须是引用表中的主键或其他候选键。
(and it goes on into more and more detail)
(并且它会越来越详细)
If you want to enforce the constraint that each row in class_attributes applies to exactly one row of classes, you need a foreign key. If you don't care about enforcing this (ie, you're fine to have attributes for non-existent classes), you don't need an FK.
如果要强制执行约束,即class_attributes中的每一行仅适用于一行类,则需要外键。如果您不关心强制执行此操作(即,您可以为不存在的类创建属性),则不需要FK。
#2
1
I don't have an answer matrix, but just for clarification purposes, we're talking about Database Normalization:
我没有答案矩阵,但为了澄清目的,我们讨论的是数据库规范化:
http://en.wikipedia.org/wiki/Database_normalization
And to a certain extent Denormalization:
并在一定程度上反规范化:
#3
1
I would say, it's the other way around. First, you design what kind of objects you need to have. For those will create a table.
我会说,这是相反的方式。首先,您需要设计需要的物体类型。对于那些将创建一个表。
Part of this phase is designing the keys, that is the combinations of attributes (columns) that uniquely identify the object. You may or may not add an artificial key or surrogate key for convenience or performance reasons. From these keys, you typically elect one canonical key, the primary key, which you try to use consistently to identify objects in that table (you keep the other keys too, they serve to ensure unicity as a business rule, not so much for identificattion purposes.)
这一阶段的一部分是设计密钥,即唯一标识对象的属性(列)的组合。出于方便或性能原因,您可能会或可能不会添加人工密钥或代理密钥。从这些密钥中,您通常会选择一个规范密钥,即主密钥,您尝试一致地使用该密钥来识别该表中的对象(您还保留其他密钥,它们用于确保作为业务规则的单一性,而不是用于识别的目的。)
Then, you think what relationships exist between the objects. An object that is 'owned' by another object, or an object that refers to another object needs some way to identify its related object. In the corresponding table (child table) you add columns to make a foreign key to point to the primary key of the referenced table.
然后,您认为对象之间存在什么关系。由另一个对象“拥有”的对象或引用另一个对象的对象需要某种方式来标识其相关对象。在相应的表(子表)中,添加列以使外键指向引用表的主键。
This takes care of all one to many relationships.
这样可以处理所有一对多的关系。
Sometimes, an object can be related multiple times to another object. For example, an order can be used to order multiple products, but a product can appear on multiple orders as well. For those relationships, you design a separate table (intersection table - in this example, order_items). This table will have a unique key created from two foreign keys: one pointing to the one parent (orders), one to the other parent (products). And again, you add the columns to the intersection table that you need to create those foreign keys.
有时,对象可以多次与另一个对象相关联。例如,订单可用于订购多个产品,但产品也可以出现在多个订单上。对于这些关系,您可以设计一个单独的表(交集表 - 在此示例中为order_items)。该表将具有从两个外键创建的唯一键:一个指向一个父键(订单),一个指向另一个父键(产品)。再次,您将列添加到交叉表中,您需要创建这些外键。
So in short, you first design keys and foreign keys, only then you start adding columns to implement them.
简而言之,您首先设计密钥和外键,然后才开始添加列来实现它们。
#4
0
Don't be concerned with the type of relationship -- it has more to do with the cardinality of the relationship.
不要关心关系的类型 - 它更多地与关系的基数有关。
If you have a one-to-many relationship, then you'd want to assign a Primary Key to the smaller of the tables, and store it as a Foreign Key in the larger table.
如果您具有一对多关系,那么您需要为较小的表分配主键,并将其作为外键存储在较大的表中。
You'd also do it with one-to-one relationships, but some people argue that you should avoid them.
你也可以通过一对一的关系来做到这一点,但是有些人认为你应该避免它们。
In the case of a many-to-many relationship, you'd want to make a join table, and then have each of the original tables have a foreign key to the join table.
在多对多关系的情况下,您需要创建连接表,然后让每个原始表都具有连接表的外键。
#1
1
Wikipedia is helpful.
*很有帮助。
In the context of relational databases, a foreign key is a referential constraint between two tables.1 The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table.
在关系数据库的上下文中,外键是两个表之间的引用约束.1外键标识一个(引用)表中的一列或一组列,这些列引用另一个列中的一列或一组列(引用)表。引用表中的列必须是引用表中的主键或其他候选键。
(and it goes on into more and more detail)
(并且它会越来越详细)
If you want to enforce the constraint that each row in class_attributes applies to exactly one row of classes, you need a foreign key. If you don't care about enforcing this (ie, you're fine to have attributes for non-existent classes), you don't need an FK.
如果要强制执行约束,即class_attributes中的每一行仅适用于一行类,则需要外键。如果您不关心强制执行此操作(即,您可以为不存在的类创建属性),则不需要FK。
#2
1
I don't have an answer matrix, but just for clarification purposes, we're talking about Database Normalization:
我没有答案矩阵,但为了澄清目的,我们讨论的是数据库规范化:
http://en.wikipedia.org/wiki/Database_normalization
And to a certain extent Denormalization:
并在一定程度上反规范化:
#3
1
I would say, it's the other way around. First, you design what kind of objects you need to have. For those will create a table.
我会说,这是相反的方式。首先,您需要设计需要的物体类型。对于那些将创建一个表。
Part of this phase is designing the keys, that is the combinations of attributes (columns) that uniquely identify the object. You may or may not add an artificial key or surrogate key for convenience or performance reasons. From these keys, you typically elect one canonical key, the primary key, which you try to use consistently to identify objects in that table (you keep the other keys too, they serve to ensure unicity as a business rule, not so much for identificattion purposes.)
这一阶段的一部分是设计密钥,即唯一标识对象的属性(列)的组合。出于方便或性能原因,您可能会或可能不会添加人工密钥或代理密钥。从这些密钥中,您通常会选择一个规范密钥,即主密钥,您尝试一致地使用该密钥来识别该表中的对象(您还保留其他密钥,它们用于确保作为业务规则的单一性,而不是用于识别的目的。)
Then, you think what relationships exist between the objects. An object that is 'owned' by another object, or an object that refers to another object needs some way to identify its related object. In the corresponding table (child table) you add columns to make a foreign key to point to the primary key of the referenced table.
然后,您认为对象之间存在什么关系。由另一个对象“拥有”的对象或引用另一个对象的对象需要某种方式来标识其相关对象。在相应的表(子表)中,添加列以使外键指向引用表的主键。
This takes care of all one to many relationships.
这样可以处理所有一对多的关系。
Sometimes, an object can be related multiple times to another object. For example, an order can be used to order multiple products, but a product can appear on multiple orders as well. For those relationships, you design a separate table (intersection table - in this example, order_items). This table will have a unique key created from two foreign keys: one pointing to the one parent (orders), one to the other parent (products). And again, you add the columns to the intersection table that you need to create those foreign keys.
有时,对象可以多次与另一个对象相关联。例如,订单可用于订购多个产品,但产品也可以出现在多个订单上。对于这些关系,您可以设计一个单独的表(交集表 - 在此示例中为order_items)。该表将具有从两个外键创建的唯一键:一个指向一个父键(订单),一个指向另一个父键(产品)。再次,您将列添加到交叉表中,您需要创建这些外键。
So in short, you first design keys and foreign keys, only then you start adding columns to implement them.
简而言之,您首先设计密钥和外键,然后才开始添加列来实现它们。
#4
0
Don't be concerned with the type of relationship -- it has more to do with the cardinality of the relationship.
不要关心关系的类型 - 它更多地与关系的基数有关。
If you have a one-to-many relationship, then you'd want to assign a Primary Key to the smaller of the tables, and store it as a Foreign Key in the larger table.
如果您具有一对多关系,那么您需要为较小的表分配主键,并将其作为外键存储在较大的表中。
You'd also do it with one-to-one relationships, but some people argue that you should avoid them.
你也可以通过一对一的关系来做到这一点,但是有些人认为你应该避免它们。
In the case of a many-to-many relationship, you'd want to make a join table, and then have each of the original tables have a foreign key to the join table.
在多对多关系的情况下,您需要创建连接表,然后让每个原始表都具有连接表的外键。