Sistem pengurusan bilik prisma

Question

Saya cuba mencipta pangkalan data untuk sistem pengurusan bilik tetapi saya keliru tentang perhubungan dan tidak dapat mencari sumber yang berguna di internet, bolehkah anda memberitahu saya jika ada masalah dengan skrip prisma ini? Kerana saya mahu mengawalnya dalam expressJs dan membuat aplikasi berdasarkannya

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
}

Saya cuba mencipta yang kasar untuk setiap entiti, tetapi akhirnya mendapat ralat perhubungan, seperti kunci asing atau sesuatu, yang bermaksud terdapat sesuatu yang tidak kena dengan perhubungan saya.

Answer 1

Skema anda adalah sah, tetapi saya mengesyorkan mengekalkan konsistensi semasa menentukan id untuk jadual. Sesetengah ID jadual ialah jenis rentetan dan beberapa ID jadual ialah jenis angka.

Berikut ialah contoh pertanyaan untuk mencipta entiti untuk model.

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();
  });

Ini jawapan pertanyaan:

> 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