Naming Conventions In MySQL Revised.
I decided to repost the rules with Rule 3 removed. After review and discussion with a few people I decided that not naming tables plural couldn’t be a rule since there are times when it’s ok to name tables plural. I downloaded MySQL 5.0 Alpha to check out information_schema views. After a few minutes of feeling them out I think they are much better than the old show syntax and seem to be more portable across other DBMS. With that I leave you with two rules:
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.
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.
March 24th, 2005 at 3:32 pm
Why would you want to name a table something just because it is easier for the optimizer?
March 24th, 2005 at 3:33 pm
Actually, why would you want to name a column something because it makes it easier for the optimizer? What about the people? Tell me about the people.
April 4th, 2005 at 4:04 pm
I find your rules to be fine for clarity and comprehension, but annoying when building SQL when joining 1 or more tables. I like to prefix my primary keys and foreign keys with pk and fk. That way I don’t have preface the field name with “TABLE.” or give the tables aliases.
I also like my TABLES and COLUMNS to be in UPPERCASE (except for the pk/fk part). It’s prettier that way. If this were a fixed width font, I would also demonstrate my lovely way of indenting my queries in code.
PS: I don’t know how any of my preferences help/hurt the optimizer.
April 14th, 2005 at 4:53 am
I think EricB’s comments are perfectly accurate.
Wes Maldonado, you’re an idiot. It’s not “only” because it’s easier for MySQL to optimize, it’s because it’s self documenting, logical, and much easier to do it this way. To anybody that DOESNT name their primary key’s and foreign keys this way - I say - you’re an idiot.
Also, prefixing your primary and foreign keys with pk & fk is just stupid. Only an idiot would do that.
Also, all uppercase is APPALLING practise. A true sign of an idiot.
March 15th, 2006 at 1:30 pm
Gavin,
I’m all for standard naming conventions because you write code for people, not the compiler. Which is why I asked about “the people” in my comments. I was poking fun at Eric because he mentions optimization in a post that is about naming conventions.
And you are correct, I’m an idiot; ask Eric.
Wes