Last week, I had to exercise the old gray muscle on some database design, and ran into a little gotcha when trying to make a high level conceptual database model in Visio.  Essentially, when you're using the ERD template, you can't model a many to many relationship without elaborating the model further with a junction table.

So that being established, I referred to my handy copy of Michael Hernandez's brilliant Database Design for Mere Mortals (by the way, I'm proud to be able to call him my friend), and some other articles including Jason Mauss' Database Naming Conventions for some opinions on naming things.

Many people suggest that the junction table name be a compound of the two tables comprising the many to many relationship (for example, DoctorsPatients).  In my opinion, this is fine only if the junction table will not contain other columns.  In many cases, the junction table will be used to record other information... order details, doctor assignments, etceteras.  In this case, I think it's better to formulate a name that describes the other content and let the two foreign keys (for the two tables with the many to many relationship) convey the many to many relationship.  For example, in the case of the Doctors and Patients, Assignments is more accurate than DoctorsPatients.

What do you think?