With database table design, Is there an advantage of using special naming convention for a Many-to-many table (a junction table)?

Question

Whenever there is a many-to-many relationship, I think there will be immediately a many-to-many table needed (also called a junction table). Is there any advantage of using special naming convention for these tables, as opposed to the other 1 to many tables? Or are there popular special naming conventions used by companies designing database tables?

Answer 1

The main advantage to any naming convention is that it allows someone, unfamiliar with a particular design, to gain immediate insight into that design. For example there are valid reasons to model the relationship between countries and languages as a one-to-one.

U.S.        English
Canada  English
France   French

Perhaps we're only interested in the most commonly spoken language in a country. There would also be valid reasons to model that relationship as one to many.

U.S.        English
Canada  English
Canada  French
France   French

If I see that there are three tables:

Languages
Countries
Languages_Countries

I don't need to look at the foreign keys to understand the underlying design. Contrast this with :

Languages
Countries
Languages_Spoken or LC_JOIN or JOIN002

You may also find yourself writing scripts to automatically create and/or drop indexes, foreign key, etc. It's much easier to do if your scripts can consistently parse a table name.

Answer 2

The advantage is that you do not have to invent funny names, and that the name of the junction table is obvious from the two referenced tables.

After years of evolving various naming schemes, I found my latest one to be the most intuitive (regarding SQL Server):

• table names are Pascal case without underscores

• junction table names concatenate the names of the joined tables with underscore

Resulting in tables Foo, FooType, Bar, BarType, and FooType_BarType and Foo_Bar.

Oracle does not provide (convenient) case sensitivity, so you have to figure out something else.

Answer 3

i tend to follow a pattern like this:

1. Table names are singular nouns
2. Link tables are nouns connected by underscore
3. a synonym can be created for the reverse order of the nouns

e.g.

ADDRESS
ORGANIZATION
ORGANIZATION_ADDRESS
synonym: ADDRESS_ORGANIZATION

this tends to simplify all query creation no matter which direction you are approaching the join from. also important is that you use full words, avoid abbreviations - this makes it simpler for everyone.