Database, Table and Column naming conventions

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.

Table Name

You should use singular nouns for database table names.

If you have a table to store the users data, this table should be called user Or users?

Let's analyze both cases to understand the difference between each approach.

Plural

• Clear that the table is storing more than one user.
• It reads well in the FROM clause

SELECT id, name from users;

Singular

• 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 ON Clause read users.country_id
• If you are using an ORM, the class you will store your data is already in singular User

Column Name

You should use camelCase for your column names, specially for Node.JS.

Snake Case snake_case

• SQL standards are case insensitive
• Readability is better

Camel Case camelCase

• It's better for data mapping. for a JSON object property user.firstName is better than user.first_name
• Specially for Javascript standards, camelCase is recommended.
• Faster typing avoiding the _
• Better Readability for short worlds
• Bad Readability for long words
  • githubAccessToken
  • facebookAccessToken

Fields

Date Fields

• Include the timezone, specially if you are working on a global project.
• As a good practice all tables should have a createdAt and updatedAt column, it will be very useful for debugging.
• Field should finish with At
  • createdAt
  • updatedAt
  • deletedAt
  • lastUpdatedAt

Primary Key

Column name must be always named id.
It's short, simple and you don't have to remember the names of the fields to join tables.

Foreign Keys

They should be a combination of the name of the foreign table and the + _id, examples:

• user_id
• post_id

Status Fields

• use booleans for single status.
  • isActive
  • isPublished
• 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)

Tools

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.

DB diagram

A free, simple tool to draw ER diagrams by just writing code.

https://dbdiagram.io

• FREE
• Online service
• Create your DB diagrams by writing
• One click sharing
• Export to Images and PDF

SQLEditor

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
• Fast
• Export to Images and PDFs