如何在同一个表中将一个记录与另一个记录相关联？ [英] How can I associate one record with another in the same table?

问题描述

我在Access中创建了一个电缆数据库，并生成一个报告，其中列出了电缆两端的连接器。每个电缆都有自己的ID和2个连接器ID，与之相关联。所有连接器来自同一个表，并链接到许多其他表。

我需要一个表（电缆）中的2个字段与第二个表中的2个记录相关联。

我的解决方案是创建2个表：主连接器表和辅助连接器表，每个表都有第一个连接器表的所有条目。然后我可以在电缆ID表中为主ID和辅助ID创建列。这个问题是我必须用相同的数据维护2个额外的表。

我是数据库理论的新手，但我想知道是否有一些高级方法来解决这个问题？

解决方案

您需要两个表格 - 一个您已经：

 电缆
 ID autoincrement主键
 ... 
  

电缆表应该只描述电缆的属性，并且不应该知道电缆如何连接到其他电缆。

第二个表格应该是电缆对之间的可能连接列表，以及可选的关于连接的描述性信息：

 连接
 Cable1ID长非空约束Connections_Cable1ID引用删除级联上的电缆（ID）
 Cable2ID长非空约束Connections_Cable2ID引用删除级联上的电缆b $ b ConnectionDesc varchar（100）
  

这种表称为联结表，映射表。它用于实现多对多关系。通常，两个表格（例如学生和课程）之间的关系是正确的，但它也可以用于在同一个表格中关联两个记录。

此设计将允许您加入电缆，连接和 Cables （再次）在单个查询中获取所需的报告。

I have created a cable database in Access, and I generate a report that has lists the connectors on each end of a cable. Each cable has its own ID, and 2 connector IDs, associated with it. All the connectors are from the same table and is linked to many other tables.

I need 2 fields in one table (cable) associated with 2 records in the second table.

My solution was to create 2 more tables: A primary connector table and secondary connector table, each of which has all entries from the first connector table. Then I could create columns in the cable ID Table for the primary and secondary IDs. The problem with this is that I have to maintain 2 extra tables with the same data.

I'm new to database theory, but I was wondering is there some advanced method that addresses this problem?

Any Help would be appreciated!

解决方案

You need two tables--one you have already:

Cables
  ID autoincrement primary key
  ...

The Cables table should just describe the properties of the cables, and should know nothing of how a cable connects to other cables.

The second table should be a list of possible connections between pairs of cables and optionally descriptive information about the connections:

Connections
  Cable1ID long not null constraint Connections_Cable1ID references Cables (ID) on delete cascade
  Cable2ID long not null constraint Connections_Cable2ID references Cables (ID) on delete cascade
  ConnectionDesc varchar(100)

This kind of table is known as a junction table, or a mapping table. It is used to implement a many-to-many relationship. Normally the relationship is between two different tables (e.g. students and courses), but it works just as well for relating two records within the same table.

This design will let you join the Cables, Connections, and Cables (again) tables in a single query to get the report you need.

这篇关于如何在同一个表中将一个记录与另一个记录相关联？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！