My MySQL naming convention

I am sometimes asked about my naming convention for MySQL. A clear naming convention has been essential for me for years because it is sometimes years that pass before I have to revisit some database that I wrote or modified for a client.

So here it is...

First some rules about applying a convention:

  1. If the database exists and has a clear naming convention then use it. Don't try to change it unless its really annoying.
  2. If the database doesn't have a convention then give it one but refactor in small steps as necessary, if at all. Use the convention for any additions or changes and make sure the convention is documented.

And now for my convention:

  1. Only use lower case letters and underscores in names. Anything else will come back and haunt you. If you are tempted to use a number (e.g. image1, image2 etc. you are doing something wrong).
  2. Use a singular clear, preferably short but mostly clear name for tables. Something like 'user' or 'book' is good.
  3. If a table is a map linking two or more tables then call it a map. Prefix it with 'map_'. Don't pluralize or otherwise obfuscate the name. 'map_user_book' is probably good.
  4. Use singular clear, preferably short but mostly clear names for columns. Do not reference the table in the name. Unless it makes natural sense to do so, do not include type information in the column name. 'id' is good. 'last_modified' is good but 'modified' is probably better. 'last_modified_timestamp' is bad. 'date_of_birth' is OK.
  5. Where a column is a foreign key, use the foreign column name refixed with the relevant table name. It will read naturally. 'user_id' is a good, common example.
  6. Name foreign keys with an 'fk_' prefix and then the name of the two tables in order. That should ensure they are always unique and easy to track down.

When building a database start with simple names. If you find you need more complexity to disambiguate stuff then refactor. The benefit of simplicity during development always outweighs the cost of the refactoring.


There are currently no comments

New Comment


required (not published)


Australia: 07 3103 2894

International: +61 410 545 357