Project: Blog Database Schema Design

In this project, we will design a relational database schema for a modern blog platform. The schema supports users, articles, hierarchical categories, tags, and comment threads.

1. Relational Database requirements

• User: Authors articles and writes comments.
• Post: Has a single author and a single category, but can have multiple tags.
• Comment: Belongs to a post and is written by a user.
• Category: Organizes articles into groups.
• Tag: Flexible keywords attached to posts.

2. Implementing the Prisma Schema File

Open prisma/schema.prisma and append these model definitions:

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String
  role      Role      @default(USER)
  posts     Post[]
  comments  Comment[]
  createdAt DateTime  @default(now())

  @@index([email])
}

enum Role {
  USER
  ADMIN
}

model Post {
  id         Int       @id @default(autoincrement())
  title      String
  slug       String    @unique
  content    String
  published  Boolean   @default(false)
  
  // Relations
  authorId   Int
  author     User      @relation(fields: [authorId], references: [id])
  categoryId Int
  category   Category  @relation(fields: [categoryId], references: [id])
  tags       Tag[]
  comments   Comment[]

  createdAt  DateTime  @default(now())
  updatedAt  DateTime  @updatedAt

  @@index([authorId])
  @@index([categoryId])
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

model Comment {
  id        Int      @id @default(autoincrement())
  text      String
  
  // Relations
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  postId    Int
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)

  createdAt DateTime @default(now())

  @@index([postId])
  @@index([authorId])
}

3. Explaining Key Schema Decisions

• Cascade Deletes: Setting onDelete: Cascade on the Comment model post relationship ensures that deleting a post automatically removes all associated comments from the database.
• Database Indexes: Adding index annotations on fields like authorId and categoryId improves read performance when querying posts by category or author.
• String Enums: Using native string enums (such as User roles) helps restrict column input values to a set of predefined roles (USER or ADMIN).