Naming Conventions In MySQL
Like every developer I have a certain way of naming things. In MySQL I have a few rules for naming tables and columns.
1. Foreign keys are always the name as the primary key it references.
2. A primary key is always named for the table my_table_id.
3. Don’t name tables plural.
Rule 1 is a self documenting technique as well as making rule #2 possible. When viewing a table having the foreign key named for the table it references tells us exactly which table to join on.
Rule 2 makes it possible to use using() when joining tables. This is not only less typing but easier for MySQL to optimize.
Rule 3 is more of a pet peeve than anything. Tables have rows, thus multiple values. It’s redundant to name them plural. Everyone knows they probably have multiple values. In the situation of a count table it should be named accordingly.
March 23rd, 2005 at 5:51 pm
Regarding rule number three… Joe Celko points out that the information_schema tables use plurals. select * from information_schema.tables . . . columns. So, what do you have to say to the standards people?
March 23rd, 2005 at 6:26 pm
Information scheme tables are views which I have no experience with, thus no opinion. The closest thing I can think of would be ’show variables’ and ’show status’ in MySQL. ’show variable’ just doesn’t sound right. I’m not trying to say don’t name anything plural, just don’t name everything plural. I suppose that would make rule 3 not a rule at all but a suggestion.
March 24th, 2005 at 11:45 pm
I generally recommend using plural for table names and singular for column names. It makes sense.
Yes, I appreciate it’s debatable from the “it’s redundant” aspect, but often redundancy serves a purpose: readability and maintainability. I reckon it’s essentially an extension of the choice between short variable names that keep code lines short, versus longer descriptive variable names.
August 21st, 2007 at 7:11 am
I agree with Eric, when writing a query it makes more grammatical sense if everything is in singular. SELECT some_field FROM some_table sounds more precise than SELECT some_fields FROM some_tables.
/2 cents
February 9th, 2008 at 3:26 am
The singular-singular approach makes very little sense to me. I always make the column names singular, but the table names plural where it makes sense.
select id, make, model from cars …
makes more sense to me than
select id, make, model from car ….
If I had a filing cabinet for cars I had owned, the folder would read “cars” or possibly “cars - 1978″, “cars - 1979″, etc. If I were partitioning.
IMHO, of course. We all do what works for us. As long as you do the same thing across the board, you won’t be too beat up.
~A!
July 11th, 2008 at 11:37 am
Everyone debates the plural/singular naming convention. The problem with plural for me and for english speakers in general is that it is not always a matter of adding an ’s’, as in situations with words like mouse and mice. This in my opinion is where the plural argument falls on its face. Great, you can read it like a sentence, but your names can get really confusing. Everyone knows a table will usually have more than one record, so do we have to say it every time.