If you are working on your own projects, a lot of the following really does not necessarily apply…however when working with a team missing off some of these basics will not make you the most liked person. Please note, these are my own preferences, that I also utilise while working with a small team.
Reserved Words
Don’t use reserved words. Although you are able to use the back-tick to be able to query tables & fields using, using reserved words isn’t really smart. When looking over your own SQL code to debug or a problem or optimise the fields, it really doesn’t help when you see
SELECT `name`, `from` FROM `from` WHERE `from` = 'England';
A list of reserved words is available at
http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-1.html
Descriptive Names
Where possible, try to use descriptive words. When looking over the structure of your table in phpmyadmin (for example) you want to be able to know what the field or table is used for.
SELECT * FROM `category`;
+----+---------+---------+-----------------------+-------------+
| id | title | slug | description | total_posts |
+----+---------+---------+-----------------------+-------------+
| 1 | Example | example | A description ....... | 0 |
+----+---------+---------+-----------------------+-------------+
The only real problem with this is when you come to using JOIN’s. When using PHP’s mysql_fetch_assoc() function, having identical field names across your 2 or more tables means you will only be getting 1 title. At this point you need to use AS to you can turn title into category_title and post_title. There is an example with AS in the Pluralisation section.
CamelCase, Under_scores, Hy-phens
Personally, I would use underscores. I’ll leave that one to you, however once you start, stick to it. It is rather annoying having inconsistency.
Pluralisation
This isn’t so much of a problem, mainly just a personal preference.
I name my database tables similar to as follows;
It makes more sense when doing a JOIN when reading, as follows
SELECT
`post`.`title` AS `post_title`,
`category`.`title` AS `category_title`
FROM `post`
LEFT JOIN `category` ON (`post`.`category_id` = `category`.`id`)
WHERE `post`.`id` = 1;
As opposed to
SELECT
`posts`.`title` AS `post_title`,
`categories`.`title` AS `category_title`
FROM `posts`
LEFT JOIN `categories` ON (`posts`.`category_id` = `categories`.`id`)
WHERE `posts`.`id` = 1;
In most cases you will be JOINing a single post to a single category. It also saves a few characters.
Foreign Keys
When defining foreign keys, as in the example above, when I reference the category in my post table, I use category_id. I use this both as a reference to the table I am running the JOIN statement with, as well as the field the JOIN takes place on.
