There are plenty of threads at discussing and proposing programming conventions, but (unless I'm entering poor search queries) I was surprised not to find much if any discussion regarding the naming conventions that mysql database designers/developers are using - particularly in regard to column (field) names.
As with most proposals for naming conventions, it's more about adopting a convention that broadly 'works' for your organisation and sticking with it. So often there's no right or wrong, better or worse, convention. For example, everyone has their own preference in regard to
- Capitalisation of the first letter of each word, eg. FirstName
- Lower case throughout, eg: firstname
- Underscores, eg: first_name
Here's what we do:
- Always lower case
- Always singular
contact (a table of contact details for people/users)
event (a table of events)
order (a table of customer orders)
Why? The plural version of a table isn't always a case of adding an 's' (so we remove this amiguity by keeping it singular), but more importantly this approach 'agrees' with our convention on column names ...
COLUMN (FIELD) NAMES
- EVERY column name to be preceded by the name of the table (including foreign keys)
- Capitalise first letter of each word
- Acronyms remain capitalised
eg. For the table name contact, the column names might be:
ContactID (primary key)
ContactCatID (this is a foreign key from the 'cat' (category) table)
Why? In answer to convention 1: we do this to ensure, 100%, that every single column name on the entire database (assuming no tables from external sources have been introduced) are unique. Sure, we know that in our php coding we can specify which column name refers to which table (eg. cat.CatID, contact.CatID), but we kind of like the fact that our column naming conventions ensure that we never have to worry about explicitly referring to 'duplicate' column names on the same db in this way.
That's broadly it for us, barring a few other minor details.
Interested in the mysql naming conventions adopted by other SitePointers...