Database Schema Design

The database schema is the blueprint of your database. It defines the structure of your data, including tables, columns, relationships, and constraints. In this section, we'll design a comprehensive schema for a task management application.

What is Prisma Schema?

Prisma schema is a declarative way to define your database structure. It's written in a special syntax that Prisma uses to generate the database client and manage migrations.

Our Task Management Schema

Let's build a complete schema for a task management application with three main entities:

User

People who create and manage tasks

Task

Individual items to be completed

Complete Schema Definition

prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  tasks     Task[]
  
  @@map("users")
}

model Category {
  id          String   @id @default(cuid())
  name        String   @unique
  description String?
  color       String   @default("#3B82F6")
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  
  tasks       Task[]
  
  @@map("categories")
}

model Task {
  id          String    @id @default(cuid())
  title       String
  description String?
  completed   Boolean   @default(false)
  priority    Priority  @default(MEDIUM)
  dueDate     DateTime?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  
  userId      String
  user        User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  categoryId  String?
  category    Category? @relation(fields: [categoryId], references: [id], onDelete: SetNull)
  
  @@map("tasks")
}

enum Priority {
  LOW
  MEDIUM
  HIGH
  URGENT
}

Understanding the Schema Components

Field Types

Type	Description	Example
String	Text data	title String
Boolean	True/false values	completed Boolean
DateTime	Date and time	createdAt DateTime
Enum	Predefined values	priority Priority

Field Attributes

@id - Marks the field as the primary key
@unique - Ensures the field value is unique across all records
@default() - Sets a default value for the field
@updatedAt - Automatically updates the field when the record is modified
? - Makes the field optional (nullable)

Relationships Explained

One-to-Many: User → Tasks

One user can have many tasks, but each task belongs to only one user.

// In User model
tasks Task[]

userId String
user   User @relation(fields: [userId], references: [id], onDelete: Cascade)

Optional One-to-Many: Category → Tasks

Tasks can optionally belong to a category. If a category is deleted, tasks become uncategorized.

// In Category model
tasks Task[]

categoryId String?
category   Category? @relation(fields: [categoryId], references: [id], onDelete: SetNull)

Delete Behaviors

Cascade - When a user is deleted, all their tasks are also deleted
SetNull - When a category is deleted, tasks' categoryId becomes null

Generating the Database

After defining your schema, you need to generate the Prisma client and create the database:

# Generate Prisma client
npx prisma generate

# Create and apply the database schema
npx prisma db push

# (Optional) Open Prisma Studio to view your database
npx prisma studio

Pro Tips

Use @@map() to customize table names in the database
Always include createdAt and updatedAt for audit trails
Use enums for fields with predefined values
Consider the delete behavior carefully for your relationships

← Previous: Setup Next: CRUD Operations →