0

enter image description here

Right now I am designing a new database for our family business. I have a few tables like in the image above, where the table only contains two foreign keys in order to represent an association between two records in two different tables.

Ex: transaction_id 123 contains the purchase of membership_id 100.

Is there a proper naming convention or a word for tables like this? For now I have been using the word "index" as a placeholder, but I am not sure if this word is appropriate.

3
6

The proper term is associative table, though most often I've heard it called is a Linker table.

Typically this table is used to support a many-to-many relationship.

As for a naming convention, that would be up to you, though I will usually just pluralize the second name, in your example I would use TransactionMemberships

4
  • This is exactly what I was looking for. I will be using linker in my names. Thank you.
    – Lil' Bits
    Jul 31 '16 at 6:31
  • "join table" is also quite common and is used (for example) in the standard Java persistence APIs (see docs.oracle.com/javaee/7/api/javax/persistence/JoinTable.html) to refer to such a table.
    – Jules
    Jul 31 '16 at 7:28
  • 3
    The term I've seen is "junction table". Jul 31 '16 at 10:27
  • 1
    Or "intersection table"
    – Joel Brown
    Aug 5 '16 at 19:19
2

Join table

is the term I have most commonly heard being used.

1

I've also seen it done as MembershipXTransaction (pronounced Membership by Transaction)

1

One other approach you should consider:

Name tables for what each record in the table represents. Use real world/business appropriate terminology as much as possible. This makes it easier for developers and business people to discuss what is in the database.

In your case, this gives you:

  • Membership - Data about memberships
  • Transactions - Data about transactions
  • Membership Transaction - Data about the interaction of memberships and transactions. This may only be the fact that the two are linked in a many-to-many relationship, but there are often other attributes of associative entities as well.

Note that simply slapping the names of the two tables on either end of a many-to-many relationship should not be your default choice. It often works out that this is the most appropriate option, but sometimes there are other names that better reflect real business terminology. For example: USER many-to-many with ROLE may be called ROLE_MEMBER.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.