mysql – What naming convention should I use in databases?

Question:

I would like to know what convention for naming tables is more appropriate and why.

Answer:

I can tell you what I use. I have not seen any rule stating that it has to be this way … or another …

  • Of course always in English .
  • Table names always uppercase and plural.
  • Field names always lowercase and singular .

The reason for the upper and lower case is to quickly identify in an SQL which is the table and which is the field that is referenced. In the statements with multiple JOINs I think it is clear that it is not a bad idea to use it like this.

The plural or singular motif. Do the tables store multiple User or Users records? It is better for me Users and does each record have a name or names ? Well, I think it is more appropriate to say name . Unless the field is a foreign key then the appropriate thing is to use small identifiers that visually help you identify that that field has something more than simple data. For example the table COMMENTS will have a field user_id (no users or id_usuario , or weird stuff). Unique reference to the foreign table USERS and indicates that there you store the ID, user_id

In the intermediate tables, for example any one to many relationship such as usuarios ( USERS ) and all uploaded photos ( PHOTOS ). I respect UPPER CASE and use the _ symbol to concatenate USERS_PHOTOS .

In some cases, in the names of the tables or of the fields, a prefix is ​​used to identify the project or the service from which that information comes or where it will be used. In the case of tables I don't like to use prefixes, but in the fields I do ( siebel_id , joomla_id , twitter_token …)

However, there are no set rules on this. That is why I have come across things like the Población table, or related tables such as Población?Ciudad or better yet Códigos postales . I think that in order not to hurt anyone, it is better to avoid accents and for "weird" concatenation characters like white space ? , - , + , …

Scroll to Top