Is there a naming convention for MySQL?

Question

Here's how I do it:

1. Table names are lower case, uses underscores to separate words, and are singular (e.g. foo, foo_bar, etc.
2. I generally (not always) have a auto increment PK. I use the following convention: tablename_id (e.g. foo_id, foo_bar_id, etc.).
3. When a table contains a column that is a foreign key, I just copy the column name of that key from whatever table it came from. For example, say table foo_bar has the FK foo_id (where foo_id is the PK of foo).
4. When defining FKs to enforce referential integrity, I use the following: tablename_fk_columnname (e.g. furthering example 3, it would be foo_bar_foo_id). Since this is a table name/column name combination, it is guaranteed to be unique within the database.
5. I order the columns like this: PKs, FKs, then the rest of columns alphabetically

Is there a better, more standard way to do this?

Answer 1

I would say that first and foremost: be consistent.

I reckon you are almost there with the conventions that you have outlined in your question. A couple of comments though:

Points 1 and 2 are good I reckon.

Point 3 - sadly this is not always possible. Think about how you would cope with a single table foo_bar that has columns foo_id and another_foo_id both of which reference the foo table foo_id column. You might want to consider how to deal with this. This is a bit of a corner case though!

Point 4 - Similar to Point 3. You may want to introduce a number at the end of the foreign key name to cater for having more than one referencing column.

Point 5 - I would avoid this. It provides you with little and will become a headache when you want to add or remove columns from a table at a later date.

Some other points are:

Index Naming Conventions

You may wish to introduce a naming convention for indexes - this will be a great help for any database metadata work that you might want to carry out. For example you might just want to call an index foo_bar_idx1 or foo_idx1 - totally up to you but worth considering.

Singular vs Plural Column Names

It might be a good idea to address the thorny issue of plural vs single in your column names as well as your table name(s). This subject often causes big debates in the DB community. I would stick with singular forms for both table names and columns. There. I've said it.

The main thing here is of course consistency!