mysql table and column (field) naming conventions

[spaceman]

Hi All,

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

1. Always lower case
2. 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

1. EVERY column name to be preceded by the name of the table (including foreign keys)
2. Capitalise first letter of each word
3. 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...

[HogMan]

For tables I have been doing the following:
1) All lowercase
2) 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
1) All lowercase
2) Try to avoid underscores
3) Singular
4) 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.

[MattR]

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.

[HogMan]

Thanks for the response MattR. What naming do you use for indexes?

[MattR]

Generally I do tablename_col1_col2

e.g.
CREATE INDEX user_userid_password ON user( userid, password )

[HogMan]

Generally I do tablename_col1_col2

e.g.
CREATE INDEX user_userid_password ON user( userid, password )

Ok, thanks for the info.

[r937]

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

rudy
http://r937.com/