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

Comments

[Aaron Reese]

My preferences from 30 years of SQL development.
Singular table names which match your class names.
Primary key is table.id and is int autoincrement. UUID is not only more storage but also slows down writes as you are not adding records to the end of the cluster but have to keep page tearing to make space in the leaf. If you want uniqueness across tables then most RDBMS have solutions. In MS-SQL this is SEQUENCE.
Foreign key is table.relatedTable_id
Putting createdAt etc at the end of the table can cause issues. If you need to add columns they will no longer be at the end. Better to actually put them immediately after the primary key fields. Also use a rownumber/rowstamp/timestamp field, especially if you are updating records using REST protocols. You can check if the payload rowstamp matches the database. If not something else has updated the record since you pulled it and your data is stale.
camelCase fields are good but can cause challenges when the standard entity is known by its acronym. CSSElement.

[hobbyman]

camelCase - no, no, and NO

I've worked in the database realm since 1993.
I have worked every major database engine and more projects than you can shake a stick at.

Any casing of column names in the long run is really more trouble than it's worth.

You either have to remember that a certain column name must be selected with double quotes or square brackets (depending on the database engine).

Ex.

select "thisColumnName" from table

or

select [thisColumnName] from table

In the long run, in my experience, you should ALWAYS use underscore between words.

Ex.

select this_column_name from table

[npac]

Thanks for sharing this informative article on database naming conventions. It's crucial to follow consistent and meaningful naming conventions for maintaining the clarity and organization of your database structure. I'd also recommend a similar tool to dbdiagram called databasediagram.com. It allows you to visually design your database diagrams with ease, while also generating SQL code directly