Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

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.

share|improve this answer
    
"link" is not available. –  SalmanPK Nov 18 '12 at 12:36
add comment

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.

share|improve this answer
    
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. –  Phelios Apr 16 '11 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 Apr 20 '11 at 10:23
add comment

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.

share|improve this answer
    
that sounds correct for this scenario. But it might get weird in others. For example, student and lecturer relationship.. how will you name this? –  Phelios Apr 15 '11 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 Apr 15 '11 at 9:07
    
Of course if it is more of a tutor/tutee relationship, then you would have a tutorTutees table. –  Matt Ellen Apr 15 '11 at 9:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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