How to associate one record with another in the same table?


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:

  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:

  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.