I'm trying to create a database for a room management system but I'm confused about the relationships and can't find any useful resources on the internet, can you tell me if there's something wrong with this prisma script? Because I want to control it in expressJs and make an app based on it
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
}
I tried creating a rough one for each entity, but ended up getting relationship errors, like foreign keys or something, which meant there was something wrong with my relationship.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Your schema is valid, but I recommend maintaining consistency when defining ids for tables. Some table IDs are string types, and some table IDs are numeric types.
The following is an example query to create entities for the 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(); });This is the query response: