Raummanagementsystem Prisma

Question

Ich versuche, eine Datenbank für ein Raumverwaltungssystem zu erstellen, aber ich bin verwirrt über die Beziehungen und kann im Internet keine nützlichen Ressourcen finden. Können Sie mir sagen, ob mit diesem Prisma-Skript etwas nicht stimmt? Weil ich es in expressJs steuern und darauf basierend eine App erstellen möchte

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
}

Ich habe versucht, für jede Entität ein grobes Schema zu erstellen, aber am Ende bekam ich Beziehungsfehler, etwa Fremdschlüssel oder ähnliches, was bedeutete, dass mit meiner Beziehung etwas nicht stimmte.

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