When designing Many-to-Many relationship on a database, we need to put another table in the middle so it become 3 tables with 1-to-many relationship. Wikipedia told me that it's called associative entity

I always have problem naming these kind of tables. Is there any convention for them? or how do you name these tables?

3 Answers 3


Say I have a customer table and an address table. Obviously a customer can have more than one address, and more than one customer can be at the same address, so I have a many to many relationship.

The name of the table that allows them to be joined together can simply be customerAddresses.

So I think the scheme I use is to split the two entities into "thing that owns" and "thing that is owned", and that gives the simple name above.

Edit: It might be better to use the analogy of "thing that acts" and "thing being acted upon" for some cases.

  • that sounds correct for this scenario. But it might get weird in others. For example, student and lecturer relationship.. how will you name this?
    – Sufendy
    Commented Apr 15, 2011 at 9:03
  • That would depend on why they are linked. I wouldn't necessarily link students to lecturers, directly. Students would have courses, and lecturers would teach courses. So I would have a studentCourses table and a lecturerCourses table. You can then use these two tables to see which lecturer is teaching which student at any given time.
    – Matt Ellen
    Commented Apr 15, 2011 at 9:07
  • Of course if it is more of a tutor/tutee relationship, then you would have a tutorTutees table.
    – Matt Ellen
    Commented Apr 15, 2011 at 9:08

Is there really no other way to get the results from another table? If there is a complex relationship between two tables, it might be simplified by a series of tables in between which already exist.

For example, supposing you have a student table and a teacher table. A student can have many teachers and a teacher can have many students.

However, before creating an associative table to link them, suppose there already exists a table classroom by which a teacher has many and a student has many.

Without creating a table for this purpose you can use table classroom. Of course this is an example which spans one table, though there may be more tables to use in order to make this connection.

  • there will still many-to-many tables in the end. just like in your example, Student and class is many to many, so do teacher and class.
    – Sufendy
    Commented Apr 16, 2011 at 1:58
  • My point was simply that if jumping from table A to table B is one to many, and from table B to table C is many to one, then the same relationship already exists without having to create a table AtoC.
    – Neil
    Commented Apr 20, 2011 at 10:23

I've seen "link". E.g., if you have Document and Checklist, and one document can be in many checklists, name the table DocumentChecklistLink. Of course it doesn't tell you whether it should be ChecklistDocumentLink or DocumentChecklistLink, I suppose you could adopt a convention of alphabetically first comes first.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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