4.4.7 复杂关系如何设计——数据建模最佳实践

一句话破题

好的数据模型不是一次设计出来的——它需要在满足当前需求和预留扩展空间之间找到平衡。

实战案例：博客系统

// 完整的博客系统模型示例
model User {
  id            String    @id @default(cuid())
  email         String    @unique
  name          String?
  avatar        String?
  role          Role      @default(USER)
  
  posts         Post[]
  comments      Comment[]
  likes         Like[]
  
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt
  
  @@index([email])
}

model Post {
  id            String    @id @default(cuid())
  title         String
  slug          String    @unique
  content       String
  excerpt       String?
  coverImage    String?
  status        PostStatus @default(DRAFT)
  publishedAt   DateTime?
  
  authorId      String
  author        User      @relation(fields: [authorId], references: [id])
  
  categoryId    String?
  category      Category? @relation(fields: [categoryId], references: [id])
  
  tags          PostTag[]
  comments      Comment[]
  likes         Like[]
  
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt
  
  @@index([authorId, status])
  @@index([slug])
}

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

model Tag {
  id        String    @id @default(cuid())
  name      String    @unique
  posts     PostTag[]
}

model PostTag {
  postId    String
  tagId     String
  post      Post      @relation(fields: [postId], references: [id], onDelete: Cascade)
  tag       Tag       @relation(fields: [tagId], references: [id], onDelete: Cascade)
  
  @@id([postId, tagId])
}

model Comment {
  id        String    @id @default(cuid())
  content   String
  
  authorId  String
  author    User      @relation(fields: [authorId], references: [id])
  
  postId    String
  post      Post      @relation(fields: [postId], references: [id], onDelete: Cascade)
  
  parentId  String?
  parent    Comment?  @relation("CommentReplies", fields: [parentId], references: [id])
  replies   Comment[] @relation("CommentReplies")
  
  createdAt DateTime  @default(now())
  
  @@index([postId])
}

model Like {
  userId    String
  postId    String
  user      User      @relation(fields: [userId], references: [id])
  post      Post      @relation(fields: [postId], references: [id], onDelete: Cascade)
  createdAt DateTime  @default(now())
  
  @@id([userId, postId])
}

enum Role {
  USER
  ADMIN
}

enum PostStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}

设计原则

1. 命名规范

类型	规范	示例
模型名	单数、PascalCase	User, Post
字段名	camelCase	firstName, createdAt
外键	关联模型名 + Id	authorId, postId
中间表	模型A + 模型B	PostTag, UserRole

2. 必备字段

model Entity {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

3. 软删除模式

model Post {
  deletedAt DateTime?
  
  // 查询时过滤
  // where: { deletedAt: null }
}

常见场景处理

多态关系（评论可以关联文章或视频）：

model Comment {
  id         String  @id @default(cuid())
  content    String
  
  // 方案1：分开外键
  postId     String?
  videoId    String?
  post       Post?   @relation(fields: [postId], references: [id])
  video      Video?  @relation(fields: [videoId], references: [id])
  
  // 方案2：通用外键 + 类型字段
  // targetId   String
  // targetType String  // "POST" | "VIDEO"
}

历史记录/审计日志：

model AuditLog {
  id        String   @id @default(cuid())
  action    String   // CREATE, UPDATE, DELETE
  entity    String   // User, Post
  entityId  String
  oldData   Json?
  newData   Json?
  userId    String
  createdAt DateTime @default(now())
  
  @@index([entity, entityId])
}

树形结构（分类/组织架构）：

model Category {
  id        String     @id @default(cuid())
  name      String
  parentId  String?
  parent    Category?  @relation("CategoryTree", fields: [parentId], references: [id])
  children  Category[] @relation("CategoryTree")
}

性能优化建议

1. 为查询条件添加索引：

   @@index([authorId, status])
   @@index([createdAt])

2. 避免过深的嵌套关系：超过 3 层考虑扁平化

3. 大字段分表：

   model Post {
     id      String       @id
     title   String
     content PostContent?
   }
   
   model PostContent {
     id      String @id
     postId  String @unique
     body    String // 大文本
     post    Post   @relation(fields: [postId], references: [id])
   }

设计检查清单

• [ ] 每个模型有 id、createdAt、updatedAt
• [ ] 外键字段命名规范
• [ ] 一对一关系外键有 @unique
• [ ] 多对多关系配置了 onDelete 行为
• [ ] 查询频繁的字段有索引
• [ ] 敏感数据考虑了软删除

本节小结

• 遵循命名规范保持一致性
• 必备字段：id、createdAt、updatedAt
• 根据业务场景选择合适的关系模式
• 为查询条件添加索引优化性能