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:
TABLE NAMES
Always lower case
Always singular
eg.
contact (a table of contact details for people/users)
event (a table of events)
order (a table of customer orders)
… etc…
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)
ContactFirstName
ContactLastName
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…
No underscores except for table prefix or table that links a many to many. So if we have a many to many between foo and bar, then the lookup table would be foo_bar.
Field Names
All lowercase
Try to avoid underscores
Singular
I do use the table name in pk’s when using a name such as id. Ie, fooid, barid.
I guess on the note of making sure all field names throughout the entire db are unique, I would like to hear from others on this. I may have the following for example:
foo
fooid (pk)
name
…
bar
barid (pk)
name
Then I just use aliases in the sql queries. I haven’t run into any issues yet.
The next item I was just searching for was index naming. Do these really matter as long as they are unique to the table itself? Ie, if I want to index the name colum, would I just name the index name? Or is it better to do index_name.
Remember that case is not important in SQL, so your table could be called FoOBaRBaZ and you could still reference it by FOOBARbaz. Same goes with column names, so it’s a matter of however you want to write them when you query.
Prefixes on table names are redundant (e.g. tblFoo, tbl_Baz), so don’t use those.
Generally I stick to entity->parameter. So, a User has a First_Name, etc.
Since SQL is case-insensitive, it helps to use underscores instead of captialization to distinguish the different words.
As long as you are consistent, there is generally no ‘best’ or ‘worst’ method. Just a little more preferable or a little less preferable.
You could name everything t1( c1, c2, c3 ), t2( c1, c2, c3 ) and that wouldn’t be ‘wrong’ per se, but I would slap you silly until you changed it.
conventions are conventions, each set is unique to the organization that set them up, and there are plenty of pros and cons no matter which way you choose
that said, i prefer plural table names
this is to drive home the point that you are not selecting from a table, but from a set of rows
the only time a singular table name makes sense to me is in the case of oracle’s DUAL, or the OOAK (one of a kind) table that i like to declare to hold application constants, where each constant is a column, and the OOAK table has only one row
prefixing the name of the column with a text fragment that indicates the name of the table is quite sloppy, in my opinion, and uses up valuable space in the column name
shorter is better, and if you need to, as matt suggested, you can qualify identically named columns with their table names or aliases