Database normalization is a common topic in database design discussions. However, it’s usually explained using complicated terms, making it hard to understand. In this article, we’ll explain what normalization means and give examples using Prisma and PostgreSQL.
The goal of database normalization is to enhance the accuracy and minimize the duplication of our data. We do that by arranging the data according to specific rules known as normal forms. These rules might sound complex initially, but they are logical and easy to understand when explained in plain language.
1NF – first normal form
The main rule of the first normal form is that each field in a table should hold just a single piece of information. Let’s look at an example to understand this:
1model User {
2 id Int @id @default(autoincrement())
3 fullName String
4 address String
5}Upon closer inspection, it becomes clear that the entity mentioned violates the first normal form rule.
In the given example, we’re putting more than one piece of information in a single column. For instance, the fullName field holds both first and last names. It’s important to consider whether we’ll need to access just part of that information. A good example is when we want to find everyone with the last name “Williams”.
Watch out, because some names have prefixes. A common example is van in the name Ludwig van Beethoven.
Similarly, keeping it as one string could be fine if we only need to show the address. But, if we ever need to retrieve users from a specific country, we’d have a problem. That’s why we must carefully consider how we set up our columns. It might be wise to play it safe and divide the data into several fields.
1model User {
2 id Int @id @default(autoincrement())
3 firstName String
4 lastName String
5 buildingNumber String
6 apartmentNumber String
7 city String
8 zipCode String
9 country String
10}Designing for scalability
It’s important to steer clear of making groups of columns with very similar names and purposes. For instance, consider a situation with articles that can have multiple authors.
1model Article {
2 id Int @id @default(autoincrement())
3 title String
4 content String
5 firstAuthor User @relation(fields: [firstAuthorId], references: [id])
6 firstAuthorId Int
7 secondAuthor User @relation(fields: [secondAuthorId], references: [id])
8 secondAuthorId Int
9}If you’re interested in learning more about setting up relationships, take a look at API with NestJS #33. Managing PostgreSQL relationships with Prisma