Schema Design: Normalization, Modeling, and Prisma

Brief Cheat Sheet

Normalization Forms

• First Normal Form (1NF): Eliminate repeating groups. Ensure each column contains atomic (indivisible) values, and each row is uniquely identifiable.
• Second Normal Form (2NF): Must be in 1NF. Remove partial dependencies; every non-primary-key attribute must depend on the entire primary key.
• Third Normal Form (3NF): Must be in 2NF. Remove transitive dependencies; every non-primary-key attribute must depend directly on the primary key, not on other non-key attributes.

Data Modeling Concepts

• One-to-One (1:1): A record in Table A is related to one specific record in Table B.
• One-to-Many (1:N): A record in Table A can be related to multiple records in Table B.
• Many-to-Many (M:N): Multiple records in Table A can relate to multiple records in Table B. This requires an intermediate junction table in relational databases.

Prisma Integration

• Schema File (schema.prisma): The declarative source of truth for your database structure.
• Models: Map directly to underlying database tables.
• Fields: Map to table columns with specific scalar types and constraints.
• Relations: Define the connections between models using the @relation attribute.

Practical Examples

Normalizing a Database Structure

Imagine a single flat table containing OrderID, CustomerName, CustomerAddress, ProductID, and ProductName. This violates normalization principles because customer details are repeated for every order they make, and product names are repeated for every product instance sold.

Solution (Achieving 3NF): Separate the data into distinct entities: Customers, Products, Orders, and OrderItems. The OrderItems table acts as a junction linking specific Orders to specific Products, eliminating data redundancy and ensuring updates (like a customer address change) only need to happen in one location.

Data Modeling with Prisma

Here is an example of modeling a One-to-Many relationship (User to Posts) and a Many-to-Many relationship (Posts to Tags) utilizing Prisma's schema definition language.

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  name  String
  posts Post[] // One-to-Many relationship
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  authorId Int
  author   User   @relation(fields: [authorId], references: [id])
  tags     Tag[]  // Many-to-Many relationship
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[] // Many-to-Many relationship
}

Applying Schema Design Principles

When designing the Prisma schema above, the following principles were applied:

1. Identify Entities: We determined User, Post, and Tag were the core independent entities.
2. Determine Relationships: A User has many Posts (1:N), and Posts have many Tags (M:N). Prisma automatically abstracts and handles the underlying junction table for implicit Many-to-Many relations.
3. Apply Constraints: We use the @unique constraint on the User email and Tag name fields to enforce data integrity directly at the schema level, preventing duplicate entries.