Search code examples
mysqldatabase-designnaming

Many to many relation table naming

I was wondering if there is some sort of standard regarding the naming of MySQL Tables? I currently have two tables, one called Users and one called Trainings. Because i need them to have a many to many relationship it is required to create a table that interconnects these two but I am unable to think of a proper naming other than Users/Trainings.

Solution

  • No official best practice, but some well tested heuristics... If my ORM or something doesn't enforce it's own standard, I like to use the underscore separated notation, but always in alphabetical order. Also, I always use singular for my classes and table names to avoid the puralization futz. Many to many by it's nature you can't tell what 'has' the other thing, so the alphabetical rule just makes it easier.

    user
address
zebra
address_user
user_zebra

    One simple set of rules, no confusion or ever as to how/what to name things, and easy to explain.

    Going a step further, I recommend unless a very specific reason compels against it:

    1. Always use lower case for tablenames and column names (that way you won't be suprised moving from a case sensitive file system to a case insensitive one--it's also easier not having to remember camel case)
    2. name primary keys for a table (when not composite keys) id
    3. name foreign keys in the format tablename_id (like user_id, zebra_id)

    Remember, just Guidelines, not dogma.

    These guidelines will make your life and your future dba's life better.