Prisma 的房间管理系统

Question

我试图为房间管理系统创建一个数据库，但我对这些关系感到困惑，并且在互联网上找不到有用的资源，你能告诉我这个 prisma 脚本是否有问题吗？因为我想在expressJs中控制它并基于它制作一个应用程序

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

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

model Guest {
  guestId     String        @id @default(uuid())
  name        String
  phone       String        @unique()
  address     String?
  nationality String
  Reservation Reservation[] @relation("GuestReservation")
}

model Reservation {
  reservationId Int             @id @default(autoincrement())
  checkIn       DateTime
  checkOut      DateTime
  Guest         Guest           @relation("GuestReservation", fields: [guestId], references: [guestId], onDelete: Cascade)
  guestId       String
  visitors      Int
  Room          Room            @relation("RoomReservation", fields: [roomId], references: [roomId], onDelete: Cascade)
  type          ReservationType
  roomId        Int
  Bill          Bill?           @relation("BillReservation")
}

enum ReservationType {
  Booking
  Contract
  Booked
  Canceled
}

model Room {
  roomId      Int           @id @default(autoincrement())
  price       Float
  type        Type
  Reservation Reservation[] @relation("RoomReservation")
}

enum Type {
  Single
  Double
  Triple
}

model Bill {
  invoiceNo     String      @id @default(uuid())
  Reservation   Reservation @relation("BillReservation", fields: [reservationId], references: [reservationId], onDelete: Cascade)
  reservationId Int         @unique()
  roomService   Float       @default(0)
  paymentMode   Payment
  Service       Service[]
}

enum Payment {
  Cash
  Visa
}

model Service {
  serviceId     String      @id @default(uuid())
  type          ServiceType
  name          String
  price         Float
  Bill          Bill        @relation(fields: [billInvoiceNo], references: [invoiceNo], onDelete: Cascade)
  billInvoiceNo String
}

enum ServiceType {
  Bar
  Laundry
}

我尝试为每个实体创建一个粗略的内容，但最终出现了关系错误，例如外来密钥之类的东西，这意味着我的关系有问题。

Answer 1

您的架构是有效的，但我建议在为表定义 id 时保持一致性。有些表的id是字符串类型，有些表的id是数字类型。

以下是为模型创建实体的查询示例。

import {
  PrismaClient,
  Type,
  ServiceType,
  Payment,
  ReservationType,
} from '@prisma/client';

const prisma = new PrismaClient({
  log: ['query'],
});

async function main() {
  // Creating a room
  await prisma.room.create({
    data: {
      price: 100,
      type: Type.Single,
      roomId: 1,
    },
  });

  // Creating a guest
  await prisma.guest.create({
    data: {
      name: 'Test',
      nationality: 'Indian',
      phone: '1234567890',
      address: 'Test Address',
      guestId: '1',
    },
  });

  // Creating a service with a bill and a reservation
  await prisma.service.create({
    data: {
      name: 'test',
      price: 100,
      type: ServiceType.Bar,
      serviceId: '1',
      Bill: {
        create: {
          paymentMode: Payment.Cash,
          invoiceNo: '1',
          Reservation: {
            create: {
              checkIn: new Date(),
              checkOut: new Date(),
              type: ReservationType.Booked,
              visitors: 1,
              roomId: 1,
              guestId: '1',
            },
          },
        },
      },
    },
  });
}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

这是查询响应：

> ts-node index.ts
prisma:query BEGIN
prisma:query INSERT INTO `white_egret`.`Room` (`roomId`,`price`,`type`) VALUES (?,?,?)
prisma:query SELECT `white_egret`.`Room`.`roomId`, `white_egret`.`Room`.`price`, `white_egret`.`Room`.`type` FROM `white_egret`.`Room` WHERE `white_egret`.`Room`.`roomId` = ? LIMIT ? OFFSET ?
prisma:query COMMIT
prisma:query BEGIN
prisma:query INSERT INTO `white_egret`.`Guest` (`guestId`,`name`,`phone`,`address`,`nationality`) VALUES (?,?,?,?,?)
prisma:query SELECT `white_egret`.`Guest`.`guestId`, `white_egret`.`Guest`.`name`, `white_egret`.`Guest`.`phone`, `white_egret`.`Guest`.`address`, `white_egret`.`Guest`.`nationality` FROM `white_egret`.`Guest` WHERE `white_egret`.`Guest`.`guestId` = ? LIMIT ? OFFSET ?
prisma:query COMMIT
prisma:query BEGIN
prisma:query INSERT INTO `white_egret`.`Reservation` (`reservationId`,`checkIn`,`checkOut`,`guestId`,`visitors`,`type`,`roomId`) VALUES (?,?,?,?,?,?,?)
prisma:query INSERT INTO `white_egret`.`Bill` (`invoiceNo`,`reservationId`,`roomService`,`paymentMode`) VALUES (?,?,?,?)
prisma:query INSERT INTO `white_egret`.`Service` (`serviceId`,`type`,`name`,`price`,`billInvoiceNo`) VALUES (?,?,?,?,?)
prisma:query SELECT `white_egret`.`Service`.`serviceId`, `white_egret`.`Service`.`type`, `white_egret`.`Service`.`name`, `white_egret`.`Service`.`price`, `white_egret`.`Service`.`billInvoiceNo` FROM `white_egret`.`Service` WHERE `white_egret`.`Service`.`serviceId` = ? LIMIT ? OFFSET ?
prisma:query COMMIT