What's the Correct Name for an "Association Table" (A Many-To-Many Relationship)

What's the correct name for an association table (a many-to-many relationship)

Cross reference table. CustomerProductXRef.

What should I name a table that maps two tables together?

There are only two hard things in
Computer Science: cache invalidation
and naming things
-- Phil Karlton

Coming up with a good name for a table that represents a many-to-many relationship makes the relationship easier to read and understand. Sometimes finding a great name is not trivial but usually it is worth to spend some time thinking about.

An example: Reader and Newspaper.

A Newspaper has many Readers and a Reader has many Newspapers

You could call the relationship NewspaperReader but a name like Subscription might convey better what the table is about.

The name Subscription also is more idiomatic in case you want to map the table to objects later on.

The convention for naming many-to-many tables is a concatenation of the names of both tables that are involved in the relation. ColourShape would be a sensible default in your case. That said, I think Nick D came up with two great suggestions: Style and Texture.

Is there a specific name for this kind of table?

It is generrally called an associative entity, but also goes by these names (listed alphabetically):

• association table
• bridge table
• cross-reference table
• crosswalk
• intermediary table
• intersection table
• join table
• junction table
• link table
• linking table
• many-to-many resolver
• map table
• mapping table
• pairing table
• pivot table
• transition table

Non-key data stored in these tables (if any) is usually described as associative data.

Proper Table naming convention for a many-to-many intersect table

I usually use the names of both of the joining tables.

So, in your case, ClientBroker.

How to properly Name an association table (One to one relationship)

The best name to give a one-to-one association table is exactly the same as the best name to give any table: whatever is the most meaningful to you and your group.

If you feel that a 1-1 cross reference table needs to have a different name than a n-m cross reference table, then take a vote. Maybe Table1Table2YRef will win (many in today's society consider a Y to be half an X).

Whatever floats your boat. Just agree on something sensible, apply it consistently and get back to work.

Btw, how many of these tables do you have. In 20+ years, I don't remember ever coming across one. There are easier ways to establish a 1-1 relationship. About the only reason I can think of where one would be useful is if the relationships break up and reform on a regular basis (kinda like a lot of marriages).

Addendum: a typical 1-1 implementation. When breaking up a large table with many fields that are rarely used, normalizing those fields to a separate table.

MainTable:

PK: 1001, F1, F2, ..., F75
PK: 1002, F1, F2, ..., F75
PK: 1003, F1, F2, ..., F75

Result:
MainTable:

PK: 1001, F1, F2, ..., F15
PK: 1002, F1, F2, ..., F15
PK: 1003, F1, F2, ..., F15

SubTable:

PK: 1001, F16, F17, ..., F15
PK: 1002, F16, F17, ..., F15
PK: 1003, F16, F17, ..., F15

create table SubTable(
    ID   int not null,
    ..., -- F16 thru f75
    constraint PK_SubTable primary key( ID ),
    constraint FK_SubTable_MainTable foreign key( ID )
         references Maintable( ID );
);

Is there an official name for the many-to-many relationship table in a database schema?

The most common name is "Join Table" in my opinion, but I have heard several others you have listed. So, I would say "no", there is no "official" name :-)

Many to many relation without association table

You're right there will only be many-to-one or one-to-many relationships. What you need is a joining table, for example:

create table A_B (
    A_id bigint references A,
    B_id bigint references B
)

Then you can have as many rows with the same A_id or B_id as you want. That results in a many-to-many relationship.

Association table between 2 association tables. Is this the correct approach?

I believe what you are looking for is what is often called "ternary relationship".

Please see these two SO questions and answers to give you implementation ideas:

• How to three-way many-to-many relationship in flask-sqlalchemy
• Inserting relationships into a table which connects 3 tables with many to many relationships with SQLALchemy - python

---

Related Topics