如何用Prisma和PostgreSQL建模固定尺寸周排班表

需求背景 #

使用Prisma连接PostgreSQL，实现用户选择可用日期与时段的功能，用户与周排班表为一对一关系，周排班表为固定结构：7天，每天17个时段（7点到23点）。

现有方案的问题 #

• JSON方案：以monday...sunday作为星期键，slot_7...slot_23作为时段键，前端渲染时必须逐个显式访问键值，无法通过循环批量处理，实现方式不够优雅。
• 二维数组方案：存在两个核心问题：
  1. PostgreSQL与Prisma均不支持固定尺寸数组，只能在业务代码中校验结构，无法将结构约束交由数据库管控；
  2. 数组无法明确标识星期起始（如周一）和时段起始（如7点），代码逻辑会依赖隐含假设，后续调整规则时极易引发错误。
• 字典方案：理想状态是兼具数组的查询效率与命名键的明确性，但Prisma和PostgreSQL均不支持原生字典类型，无法直接使用。

迭代优化过程 #

• Update 1：尝试采用自标识对象数组方案，该方案兼顾了数组的灵活性与键名的明确性，但存在重复对象的风险，需要通过SQL和Prisma的唯一约束来规避重复数据。
• Update 2：最终确定的Prisma模型如下：

model User {
   id           String          @id @default(uuid())
   createdAt    DateTime        @default(now())
   updatedAt    DateTime        @updatedAt
   email        String          @unique
   passwordHash String
   schedule     DailySchedule[]
}

model DailySchedule {
   id     String  @id @default(uuid())
   day    String  // 可限定枚举值，如'MONDAY'/'TUESDAY'等增强约束
   times  Times[]
   user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)
   userId String

   @@unique([userId, day]) // 确保单个用户同一星期仅存一条记录
}

model Times {
   id              String        @id @default(uuid())
   time            Decimal       // 存储时段起始时间，如7.0、8.0对应7点、8点
   free            Boolean       // 标记该时段是否可用
   dailySchedule   DailySchedule @relation(fields: [dailyScheduleId], references: [id], onDelete: Cascade)
   dailyScheduleId String

   @@unique([dailyScheduleId, time]) // 确保单天同一时段仅存一条记录
}

模型优势 #

通过@@unique约束分别保证：

1. 单个用户的同一星期日期仅对应一条DailySchedule记录；
2. 单天的同一时段仅对应一条Times记录；
   完全避免了重复数据，同时结构清晰，前端可通过循环遍历DailySchedule和Times数组批量渲染，解决了原JSON方案的痛点，也将结构约束交由数据库管控，降低了代码依赖风险。

内容的提问来源于stack exchange，提问作者André Casal