API with NestJS #172. Database normalization with Drizzle ORM and PostgreSQL

October 28, 2024

This entry is part 172 of 172 in the API with NestJS

Database normalization is a key part of database design, but it’s often explained in complicated terms. In this article, we’ll break down the normalization rules and provide down-to-earth examples using Drizzle ORM and PostgreSQL.

Database normalization aims to avoid unnecessarily duplicating our data and make it easier to manage. It does that through specific rules called the normal forms. They might seem complex at first, but they are pretty simple once explained in a straightforward way.

1NF – first normal form

The main principle of the first normal form is that each field in a table should hold only a single piece of information.

database-schema.ts

import { serial, text, pgTable } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {

id: serial('id').primaryKey(),

fullName: text('fullName').notNull(),

address: text('address').notNull(),

});

export const databaseSchema = {

users,

};

When we look at the above schema, we can see that it violates the first normal rule.

In our example, we’re putting more than one piece of information into a single column. For example, the fullName column holds both the first and last name. We should consider if we will need to access just a part of the information. For example, we might want to find all users with the last name “Williams”.

We need to watch out for names that have prefixes. One of the most recognizable examples is Ludwig van Beethoven.

Also, keeping the address in one string could be fine if we only want to treat it as a whole. However, if we need to retrieve all users from a particular country, for example, we’d have a problem. It might be a good idea to divide our data into multiple fields.

database-schema.ts

import { serial, text, pgTable } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {

id: serial('id').primaryKey(),

firstName: text('firstName').notNull(),

lastName: text('lastName').notNull(),

buildingNumber: text('buildingNumber').notNull(),

apartmentNumber: text('apartmentNumber').notNull(),

city: text('city').notNull(),

zipCode: text('zipCode').notNull(),

country: text('country').notNull(),

});

export const databaseSchema = {

users,

};

With this approach, we could easily find all users from a given city, for example.

Aiming for scalability

We should avoid making groups of columns that have very similar names and purposes. For example, let’s take a look at articles that can have multiple authors.

database-schema.ts

import { serial, text, integer, pgTable } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {

id: serial('id').primaryKey(),

// ...

});

export const articles = pgTable('articles', {

id: serial('id').primaryKey(),

title: text('title').notNull(),

content: text('content').notNull(),

firstAuthorId: integer('firstAuthorId')

.references(() => users.id)

.notNull(),

secondAuthorId: integer('secondAuthorId')

.references(() => users.id)

});

export const databaseSchema = {

articles,

users,

};

Unfortunately, this solution is not scalable. As soon as one of the articles has a third author, we would need to add another column. Also, to find articles written by a particular user, we would have to check each column.

To deal with this solution, we should implement a many-to-many relationship.

database-schema.ts

import {

serial,

text,

integer,

pgTable,

primaryKey,

} from 'drizzle-orm/pg-core';

export const users = pgTable('users', {

id: serial('id').primaryKey(),

// ...

});

export const articles = pgTable('articles', {

id: serial('id').primaryKey(),

title: text('title').notNull(),

content: text('content').notNull(),

});

export const usersArticles = pgTable(

'usersArticles',

{

userId: integer('userId')

.notNull()

.references(() => users.id),

articleId: integer('articleId')

.notNull()

.references(() => articles.id),

(columns) => ({

pk: primaryKey({ columns: [columns.userId, columns.articleId] }),

}),

);

export const databaseSchema = {

articles,

users,

usersArticles,

};

If you want to know more about many-to-many relationships with the Drizzle ORM, check out API with NestJS #154. Many-to-many relationships with Drizzle ORM and PostgreSQL

This approach allows us to have as many authors as we like for a given article.

JSON columns and arrays in PostgreSQL

In the previous parts of this series, we stored arrays and JSON data using Drizzle ORM and PostgreSQL. Using JSON and array columns can be helpful, but storing an entire array or a JSON dictionary in a single column can be treated as breaking the first normal form rule. However, it does not automatically mean our database is flawed because we must choose the right tool for each task. If, in some cases, using an array can make our database more straightforward to manage, we can go for it.

2NF – second normal form

While we usually use IDs as primary keys, we could also use composite primary keys that consist of more than one column.

The crucial principle of the second normal form is that every piece of information in the table should be related to the entire primary key and not just a part of it.

Also, to meet the requirements of the 2NF our data must already comply with the 1NF.

In our example, we have a composite primary key that consists of the clientName and productName columns.

database-schema.ts

import {

serial,

text,

integer,

pgTable,

primaryKey,

} from 'drizzle-orm/pg-core';

export const productPurchases = pgTable(

'productPurchases',

{

id: serial('id').primaryKey(),

productName: text('productName').notNull(),

productPrice: integer('productPrice').notNull(),

(columns) => ({

pk: primaryKey({ columns: [columns.productName, columns.productPrice] }),

}),

);

export const databaseSchema = {

productPurchases,

};

The issue is that the productPrice depends on only a part of our primary key – the productName. It does not matter who bought the microwave – it still costs the same. This causes the information about the price of the microwave to be duplicated, taking extra space in our database and causing various problems. For example, if we need to adjust the product cost, we must do that in multiple rows.

3NF – third normal form

To meet the requirements of the third normal form, every piece of information in our table should depend only on the primary key and not on other columns in our table.

In the above example, we have the courierCarModel and courierCarBrand columns that depend on each other. If a particular car model is “Prius”, we know that the brand should be “Toyota”. This breaks the third normal form.

database-schema.ts

import {

serial,

text,

integer,

pgTable,

primaryKey,

} from 'drizzle-orm/pg-core';

export const productPurchases = pgTable(

'productPurchases',

{

id: serial('id').primaryKey(),

productName: text('productName').notNull(),

productPrice: integer('productPrice').notNull(),

courierCarBrand: text('courierCarBrand').notNull(),

courierCarModel: text('courierCarModel').notNull(),

(columns) => ({

pk: primaryKey({ columns: [columns.productName, columns.productPrice] }),

}),

);

export const databaseSchema = {

productPurchases,

};

If we design the database in the above way, keeping the data consistent is challenging. For example, changing the brand of the car in one row and not everywhere causes the same model of a car to belong to multiple different brands in different rows.

Lessons learned from 2NF and 3NF

The second and third normal forms teach us that a particular table should describe one specific entity. Instead of putting all the data into one table, we can make several tables that relate to each other. Also, it usually makes sense to have an ID column generated automatically instead of using primary keys that consist of multiple columns.

Let’s rewrite our table and use what we learned from the second and third normal forms. This includes creating separate tables for products, clients, card brands, and car models. We can then create many-to-one relationships between them.

If you want to know more about many-to-one relationships with the Drizzle ORM, check out API with NestJS #151. Implementing many-to-one relationships with Drizzle ORM

database-schema.ts

import { serial, text, integer, pgTable } from 'drizzle-orm/pg-core';

export const products = pgTable('products', {

id: serial('id').primaryKey(),

price: integer('price').notNull(),

});

export const clients = pgTable('clients', {

id: serial('id').primaryKey(),

});

export const carBrands = pgTable('carBrands', {

id: serial('id').primaryKey(),

});

export const carModels = pgTable('carModels', {

id: serial('id').primaryKey(),

carBrandId: integer('carBrandId')

.references(() => carBrands.id)

.notNull(),

});

export const productPurchases = pgTable('productPurchases', {

id: serial('id').primaryKey(),

productId: integer('productId')

.references(() => products.id)

.notNull(),

clientId: integer('clientId')

.references(() => clients.id)

.notNull(),

courierCarModelId: integer('courierCarModelId')

.references(() => carModels.id)

.notNull(),

});

export const databaseSchema = {

products,

clients,

carBrands,

carModels,

productPurchases,

};

With this solution, the table with product purchases table contains only the IDs that relate to various other tables.

The costs of products can change over time. If you want to store the exact cost the client paid when they made a purchase, you can add a column with the price to the above table.

Summary

In this article, we covered database normalization basics and used examples with the Drizzle ORM to explore various normal forms. While the official definitions can be complex, the core ideas behind data normalization are quite simple. Keeping them in mind can help us ensure that our database is scalable, efficient, and easy to maintain.

Series Navigation<< API with NestJS #171. Recursive relationships with Drizzle ORM and PostgreSQL