Database, Table and Column naming conventions
Tags: node, php, javascript, database
Published: 2021-04-03 09:47:23

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

```sql
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:

```sql
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`


![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rsd9ka1zd3upeim1uper.png)

---
## Column Name


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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rxsj6png7c3v7fqjuu1a.png)

#### 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](https://www.w3schools.com/js/js_conventions.asp#:~:text=camelCase%3A,with%20many%20JavaScript%20library%20names.).
- 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](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/](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