如何在2个表之间建立这种关系 [英] How to set up this kind of relationship between 2 tables
问题描述
大家好，
Hi everyone,
我第一次遇到这种情况，并不太确定如何设置它。
first time I face this kind of scenario, and not quite sure how to set it up.
我有一个'产品'表和'product_groups'表。就我而言，一组只是将一定数量的产品组合在一起。组始终具有父产品，该组中的所有其他产品都被视为子产品。另外产品
只能是一组的一部分。
I have a 'products' table and a 'product_groups' table. In my case, a group is simply a certain number of products grouped together. A group always has a parent product, and all other products in that group are refereed to as child products. Also products can only be part of one group.
所以说，我设置了2个表，如下所示（简化）：
So with that said, I set up 2 tables as shown below (simplified):
tbl_products：
product_id
product_name
tbl_products:
product_id
product_name
tbl_product_groups：
group_id
parent_product_id
child_product_id
tbl_product_groups:
group_id
parent_product_id
child_product_id
我想要的是什么do是在'product_id'和'parent_product_id'之间建立一对多的关系，在'product_id'和'child_product_id'之间创建一对一的关系，但是Access不允许我与同一个字段创建两个关系'product_id'。
What I wanted to do was create a 1-to-many relationship between 'product_id' and 'parent_product_id', and a 1-to-1 relationship between 'product_id' and 'child_product_id', however Access does not allow me to create two relationship with the same field 'product_id'.
请注意，我实际上并不需要字段'group_id'...如果最佳方法是使用复合密钥，则可以删除此字段。
Note that I do not actually need the field 'group_id'...this field could be removed if the best approach is to use a composite key.
非常感谢任何帮助。谢谢！
Any help would be very appreciated. Thanks!
推荐答案
大家好，
Hi everyone,
时间我面对这种情况，并不太确定如何设置它。
first time I face this kind of scenario, and not quite sure how to set it up.
我有一个'产品'表和'product_groups'表。就我而言，一组只是将一定数量的产品组合在一起。组始终具有父产品，该组中的所有其他产品都被视为子产品。另外产品
只能是一组的一部分。
I have a 'products' table and a 'product_groups' table. In my case, a group is simply a certain number of products grouped together. A group always has a parent product, and all other products in that group are refereed to as child products. Also products can only be part of one group.
所以说，我设置了2个表，如下所示（简化）：
So with that said, I set up 2 tables as shown below (simplified):
tbl_products：
product_id
product_name
tbl_products:
product_id
product_name
tbl_product_groups：
group_id
parent_product_id
child_product_id
tbl_product_groups:
group_id
parent_product_id
child_product_id
我想要的是什么do是在'product_id'和'parent_product_id'之间建立一对多的关系，在'product_id'和'child_product_id'之间创建一对一的关系，但是Access不允许我与同一个字段创建两个关系'product_id'。
What I wanted to do was create a 1-to-many relationship between 'product_id' and 'parent_product_id', and a 1-to-1 relationship between 'product_id' and 'child_product_id', however Access does not allow me to create two relationship with the same field 'product_id'.
请注意，我实际上并不需要字段'group_id'...如果最佳方法是使用复合密钥，则可以删除此字段。
Note that I do not actually need the field 'group_id'...this field could be removed if the best approach is to use a composite key.
非常感谢任何帮助。谢谢！
Any help would be very appreciated. Thanks!
我可能会弄错，但从你所说的话来看，我认为（除非有）为产品组存储的更多信息）您可以只使用一个表，这样的产品表：
I could be mistaken, but from what you said it seems to me that (unless there's more information to be stored for a product group) you could do with just one table, a Products table like this:
产品
----------
$
产品ID（主键）
ProductName（text）
ParentProductID（Products的外键 - 自连接键，可为空）
Products
----------
ProductID (Primary Key)
ProductName (text)
ParentProductID (foreign key to Products -- a self-join key, nullable)
如果产品的ParentProductID为Null，则它不是组的成员。 具有相同ParentProductID的所有产品组成该产品的子组。
If a product's ParentProductID is Null, then it is not a member of a group. All the products that have the same ParentProductID compose the group that are children of that product.
您可以通过将表两次添加到Relationships窗口来设置表与其自身之间的关系，并创建两个副本之间的关系。 该表的第二个副本将具有后缀 "_1"添加到最后，
来区分这两个副本，但它仍然是同一个表。
You can set up a relationship between a table and itself by adding the table twice to the Relationships window, and creating the relationship between the two copies. The second copy of the table will have a suffix "_1" added to the end, to distinguish the two copies, but it's still the same table.
同样，在设计查询时，您可以将表的两个副本添加到设计网格中 第二个副本将自动被赋予带后缀的别名，但如果需要，您可以更改别名。
Similarly, when designing queries you can add two copies of the table to the design grid. The second copy will automatically be given an alias with a suffix, but you can change the alias if you want.
这篇关于如何在2个表之间建立这种关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
