A naming conversion is a set of rules you should follow to increase your data models' readability.
In this post, we will talk about the best practice I have found and followed along on my career as a developer (PHP, Node.JS, MySQL, PostgreSQL) and the reasons for each one of them.
In the end, it's not important which one you follow, but you should follow a naming convention.
You should use singular nouns for database table names.
If you have a table to store the users data, this table should be called
Let's analyze both cases to understand the difference between each approach.
- Clear that the table is storing more than one user.
- It reads well in the
SELECT id, name from users;
- We are not naming a table, we are naming a relation.
- It reads well everywhere else in the SQL query:
SELECT id, name FROM user JOIN country ON ( user.countryId = country.id ) WHERE country.name = "Japan"
- It would make less sense if the
- If you are using an ORM, the class you will store your data is already in singular
You should use camelCase for your column names, specially for Node.JS.
- SQL standards are case insensitive
- Readability is better
- It's better for data mapping. for a JSON object property
user.firstNameis better than
- Faster typing avoiding the _
- Better Readability for short worlds
- Bad Readability for long words
- Include the timezone, specially if you are working on a global project.
- As a good practice all tables should have a
updatedAtcolumn, it will be very useful for debugging.
- Field should finish with
Column name must be always named
It's short, simple and you don't have to remember the names of the fields to join tables.
They should be a combination of the name of the foreign table and the +
- use booleans for single status.
- Use enums if you need few columns that can be true or false at the same time.
- post.status (draft, inactive, published)
- user.status (inactive, active, banned)
- product.status (draft, in_review, approved, disapproved)
Here are two tools I recommend to design your database schemas. I like them because their UI/UX is good, and I really enjoy using them.
A free, simple tool to draw ER diagrams by just writing code.
- Online service
- Create your DB diagrams by writing
- One click sharing
- Export to Images and PDF
Makes database design easier than ever! https://www.malcolmhardie.com/sqleditor/
- FREE (for 30 days, but you can continue using)
- Paid ($80 single user license)
- Mac app
- Great usability
- Export to Images and PDFs