technology

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
 
notion image
 

Column Name

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

 
notion image
 

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
  • 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