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.