Good practice guidelines for name elements in a database

db

A friend told me about good practice guidelines for name a database. This is like everything in the life, it takes experience, but the basics I usually follow would be summarized as:


  • Each table with a field called id autonumeric that is Primary Key (PK). ALL tables must ALWAYS have PK.

  • Though it's obvious, no accents.

  • Names of fields and descriptive tables, in lowercase and with underscores. Not PascalCase, camelCase, kebab-case. Try to use snake_case.

  • Name of the tables in plural. For example: users, animals. If there is a pivot table of an N:N relationship, for example between users and roles, it is sorted in alphabetical order and singular. In this case, user_role.

  • Foreign keys will have the suffix id. In the previous pivot table, we would have in the userrole table: userid and role_id.

  • The names of the fields must be descriptive and without shortening as far as possible, in singular. No observ, so observation. Although there are exceptions if they are well known: dto (=discount), c_postal (postal code),....

  • No Primary Foreign Keys or compound PKs.

Do you have any more advice?. See you in the comments.


I request your permission to obtain statistical data on its navigation on this website. If you continue to browse I consider you accept the use of cookies. This site is adapted to be compliant with the new law about General Data Protection Regulation. OK | More Information