39

What's the correct or most popular name for an "association table"?

I've heard lookup, associative, resolving, mapping and junction table.

Community
  • 1
  • 1
Imran
  • 11,350
  • 20
  • 68
  • 78
  • 2
    I think I've heard it called "link table" as well, but it has too much overlap and ambiguity with the concept of linked tables – Nelson Rothermel Jun 15 '10 at 15:22
  • Possible duplicate of [What should I name a table that maps two tables together?](http://stackoverflow.com/questions/1813321/what-should-i-name-a-table-that-maps-two-tables-together). There is no absolute "correct" name, just synonyms that all mean the same thing: Xreference(XREF), map, lookup, associative, etc – OMG Ponies Jun 15 '10 at 15:55
  • 2
    http://en.wikipedia.org/wiki/Junction_table says : Junction tables are known under many names, among them cross-reference table, bridge table, join table, map table, intersection table, linking table, many-to-many resolver, link table, pairing table, pivot table, transition table, crosswalk, or association table. – AjV Jsy Sep 04 '14 at 09:50

8 Answers8

37

There is no "correct" name, but the academic name would be an "Associative Table" (see See the Wikipedia article Associative Entity). Other common names are (in alphabetical order):

  • 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
  • Pivot Table
  • Pairing table
  • Relationship table
  • Transition table

Note: This is contents original created by Derek Greer but that was posted as an edit to an accepted answer that totally changed the answer.

wibeasley
  • 5,000
  • 3
  • 34
  • 62
Dijkgraaf
  • 11,049
  • 17
  • 42
  • 54
  • Created answer that were rollbacked out of the accepted answer as per https://meta.stackoverflow.com/questions/337963/totally-changing-an-accepted-answer/337966 – Dijkgraaf Nov 16 '16 at 03:03
  • I also use **pivot** table. Depending on company standards I might just use `student_courses` for a many-to-many on tables `students` and `courses`. Some standards I've encountered might enforce a prefix for this; such as `lnk`, `ref`, `pvt`, etc... e.g. `lnk_students_courses`. – Rockin4Life33 Sep 04 '18 at 20:46
14

Cross reference table. CustomerProductXRef.

Dijkgraaf
  • 11,049
  • 17
  • 42
  • 54
Raj Kaimal
  • 8,304
  • 27
  • 18
  • 6
    I have to say I'm totally against your naming convention. If you accept that such a table is a relationship table then the table name should reflect the relationship. I think that most data modellers would call the relationship between customers and products 'Orders'. – onedaywhen Jun 16 '10 at 07:36
  • 3
    @onedaywhen "Orders" is normally an example of Many-to-One, not Many-to-Many despite the unfortunate reference to "Products". Furthermore, it might make sense to differentiate "Customer Orders" from "Purchase Orders". In the case of One-to-Many, I'd opt NOT to use a junction table conditions permitting. To your criticism, "Customer Product" is indeed an unusual term combination for a table name. – bvj Jul 14 '14 at 18:18
13

"Correct" depends on the modeling methodology in use. I am familiar with Chen, in which this table is the physical implementation of an Associative Entity. I suppose most popular would be directly related to most popular modeling methodology.

Wikipedia lists several names for this type of table.

DCookie
  • 42,630
  • 11
  • 83
  • 92
10

I was taught and use the term "Join Table"

Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
4

Depends on whom you ask. They're all correct, use the term that makes the most sense to who you're talking to.

Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
Alex Larzelere
  • 1,211
  • 1
  • 11
  • 17
4

Relationship table.

"One of the basic tricks in SQL is representing a many-to-many relationship. You create a third table that references the two (or more) tables involved by their primary keys. This third table has quite a few popular names, such as 'junction table' or 'join table,' but I know that it is a relationship."

Hollywood Couples by Joe Celko

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
3

Do you call your customer table CustomerTable or Customer or Customers? I generally use a "business object" name (eg Orders for information about which customers have ordered which products, not CustomerProduct) but a table that really just tracks the relationship, like SalesRepCustomer, I give the name of the two tables involved and don't add a suffix. As others say, be consistent.

I reserve the name lookup (in conversation, not in the table name) for things like "what is the name of Country 11", not for "which sales rep looks after Country 11".

Kate Gregory
  • 18,808
  • 8
  • 56
  • 85
  • +1 HOWEVER for a table that tracks a relationship, the 'business' will usually already have a name for that relationship e.g. for the business I work for, the Sales staff who look after specific customers are known as 'account managers'. – onedaywhen Jun 16 '10 at 07:48
1

We call these Crosswalk tables where I work. Naming is based on Table1XTable2 where the contents are the PKs of the 2 tables.

wergeld
  • 14,332
  • 8
  • 51
  • 81