When creating a database, best practices should be used to facilitate reading, prevent bugs and avoid errors during development. This article presents some best practices when designing a data schema. These good practices are based both on conventions recommended by the greatest number and on personal experience.
To note : these recommendations are not intended to be adopted unanimously, it is advisable for each one to define his own conventions when to stick to them.
Here is a list of best practices that apply for both table and column naming:
- Do not use reserved words. For example, you should avoid naming a column “date” because this word is already used.
- Do not use special characters
- Avoid capital letters. To write 2 words, you must use an underscore. For example, you should use “date_inscription” rather than “DateInscription”
- Avoid the use of abbreviations. At first glance it is useful to make table names not very long, but in practice a developer will not easily understand what this corresponds to and he should view the content to understand what is behind it.
Here is a list of best practices:
- Use a representative name of the content
- Use only one word when possible
- Favor the singular (but sometimes it’s a big debate …)
- Think about generic names and consider future developments. For example, a “customer” table which could also contain prospects and salespeople should rather be called “user”
- Prefix the names of the tables
- Prevents accidental use of reserved words.
- Used to avoid conflicts when there are several similar programs on the same database (for example, if 2 programs each use a table called “user”).
- Useful to easily separate tables associated with one system or another. For example if a WordPress blog and a Prestashop e-commerce store are placed on the same database, the blog will have tables starting with “wp_” while the store will have tables starting with “ps_”.
- It’s easier to re-install a backup. For example, to reinstall a backup of the blog, it is possible to add tables starting with “wp2013_” then modify the application code to migrate everything at once.
- On large projects it can be practical so that all tables associated with users start for example with “user_”, all those concerning products with “product_” and so on.
Here is the list of best practices:
- Prefix all columns the same for each table. This is much more convenient when it comes to making joins.
- In the case of a multilingual site: indicate language and geography area for alphanumeric fields (fr_fr for French from France, fr_ca for French from Canada, fr_be for French from Belgium…). It is extremely useful if one day an application has to become multilingual. If the database needs to internationalize, it will suffice to add an additional column with the translations.
- When a foreign key is used (English translation: “Foreign Key”), it is convenient to indicate it in the name of the column. The column can contain the prefix, then “fk” for Foreign Key, then the name of the table and finally end with “id”. Thus, a column could be called “wp_fk_user_id” (cf. prefix “wp”, foreign key on the user table of the “id” column).
- Still similarly titled some fields such as DATE or DATETIME. This helps a developer know what a field will contain without necessarily looking at the content.
Imagine the tables of a forum. There can be 3 tables: one for forums, another for questions, and one for messages. These tables are linked together using foreign keys.
Below there are 2 distinct ways of naming the tables of such an application:
The example I recommend is the example on the right. This example uses prefixes and follows a good naming convention to know what columns can contain.
Here is an example of a query with the wrong name:
SELECT `post`.`id` AS post_id, `post`.`contenu` AS post_contenu, `topic`.`id` AS topic_id, `topic`.`nom` AS topic_nom FROM `post` INNER JOIN `topic` ON `topic`.`topic_id` = `post`.`id`
This query is a bit long and complicated. For example, you must use the name of the table to avoid errors where SQL cannot differentiate which column is called. This is essential to avoid the error: “ambiguous column name”.
This SQL query can be simplified by using naming rules:
SELECT `p_id`, `p_description_fr_fr`, `t_id`, `t_nom_fr_fr` FROM `f_post` INNER JOIN `f_topic` ON `t_id` = `p_fk_topic_id`
What else ?
If you use other naming rules, feel free to share them in the comments. If you use rules that conflict with these, please share them as well.