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.
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?
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.
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.
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
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!
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.
But surely thats the point. Everyone knows its plural – so make it a plural.
SELECT image_name FROM images WHERE…
like you rightly said – plural.
Whats the official idea? does anybody have a link to naming conventions set by Sun?
If you are dynamically handling table and Ids and foreign keys the use of plural tables is garbage. As stated above, plurality in English follows no standard which makes programatically accessing tables to keys impossible. This isn’t about pretty sentences. It’s code, not a story tale…
Probably the best argument I can think of for avoiding plurals is in the case of subsequent tables (sub-tables) for the same basic object (e.g. client, client_group, client_user). The client table could accurately be called the parent table or controlling table and in a db GUI they will always be at the top of your list if named singular. On the contrary however, when using plurals your parent table is likely to fall below your sub-tables (e.g. client_group, client_user, clients).
Singular, is just my preference, but I thought I’d throw it out there for you all.
Tables must be plural because they are repositories of data, a container of data. Singular name is used to refer an entity. This is why a object is named Car and the database table is CARS. Check the Oracle Naming conventions in http://www.oracle-base.com/articles/misc/NamingConventions.php
I have been designing databases for a long time, and the issue of naming seems to be one where everyone has their own opinion. So long as we stick to English as the natural language, we will always have issues – no matter which naming scheme we choose:
1) Table-names singular – not ideal in all circumstances:
select count(*) from goose; // Logically, a “goose” has a count of 1.
2) Table-names plural – also not ideal, because English plurals are not consistent:
select geese.name from geese; // This looks like we are trying to select a name that is common to all geese, rather than the name of a single goose.
3) Table-names as containers – this would have to be the least useful convention:
select gaggle.location from gaggle; // How many reeaders of our code are going to know that a row in gaggle represents a goose?
I would generally run with either option 1 or 2, depending on the client and the situation. So long as for a given system (could involve more than one database) you are consistent, it really doesn’t matter.
I enforce the “no plural” theory just because I often use ORMs such as Propel or Doctrine. These auto-generate functions that fetch relationships between tables, and these kind of function names often append an “s”. So if I was to call a table “my_users”, I’d end up with an automatically generated function name “getMyUserss()”… which just looks weird.