How to model a weak entity in sql-CodePudding

I am new to databases in general and currently just trying stuff out.

I am trying to model a basic hotel system in SQL

So my plan is as follows.

A Hotel has many rooms and a room has a room number which is unique within a hotel. I understand this is a weak entity

When a guest creates a booking, a room is assigned to that guest.

So I came up with the following create statements to create the database

CREATE TABLE Hotel (
  hotel_id int PRIMARY KEY,
  name varchar(255),
  stars int
);

CREATE TABLE Room (
  room_number int,
  capacity int,
  hotel_id int
);

CREATE TABLE Guest (
guest_id int PRIMARY KEY,
name varchar(255),
hotel int,
room int
);

ALTER TABLE Room ADD FOREIGN KEY (hotel_id) REFERENCES Hotel (hotel_id) ON DELETE CASCADE;

ALTER TABLE Guest ADD FOREIGN KEY (hotel) REFERENCES Hotel (hotel_id);

ALTER TABLE Guest ADD FOREIGN KEY (room) REFERENCES Room (room_number);

I get the error no matching unique or primary key for this column-list

I guess it's because of the fact that room_number is no primary key.

My problem is that if I make it a primary key, that I cannot have two rooms at different hotels with the same room number as a primary key is unique

How can I fulfill the requirement that room is a weak entity but still have unique room numbers within a hotel.

I take it I am missing something. Help greatly appreciated

CodePudding user response：

Create a primary key that is a combination of hotel_id and room_number.

CREATE TABLE Hotel (
  hotel_id int PRIMARY KEY,
  name varchar(255),
  stars int
);

CREATE TABLE Room (
  room_number int,
  capacity int,
  hotel_id int,
  PRIMARY KEY (room_number, hotel_id)
);

CREATE TABLE Guest (
guest_id int PRIMARY KEY,
name varchar(255),
hotel int,
room int
);

ALTER TABLE Room ADD FOREIGN KEY (hotel_id) REFERENCES Hotel (hotel_id) ON DELETE CASCADE;

ALTER TABLE Guest ADD FOREIGN KEY (hotel) REFERENCES Hotel (hotel_id);

ALTER TABLE Guest ADD FOREIGN KEY (room, hotel) REFERENCES Room (room_number, hotel_id);

CodePudding user response：

Create a primary key for the rooms table based on an identifier other than the room_number and then have a bookings table as guests can make multiple bookings:

CREATE TABLE Hotels (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(255),
  stars INT
);

CREATE TABLE Rooms (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  hotel_id    INT,
  room_number INT,
  capacity    INT,
  CONSTRAINT rooms__hi_rn__u UNIQUE(hotel_id, room_number)
);

CREATE TABLE Guests (
  id       INT AUTO_INCREMENT PRIMARY KEY,
  name     varchar(255)
);

CREATE TABLE Bookings (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  guest_id       INT,
  room_id        INT,
  check_in_date  DATE,
  check_out_date DATE
);

ALTER TABLE Rooms ADD CONSTRAINT rooms__hi__fk
    FOREIGN KEY (hotel_id) REFERENCES Hotels (id);

ALTER TABLE Bookings ADD CONSTRAINT bookings__gi__fk
    FOREIGN KEY (guest_id) REFERENCES Guests (id);

ALTER TABLE Bookings ADD CONSTRAINT bookings__ri__fk
    FOREIGN KEY (room_id) REFERENCES Rooms (id);

db<>fiddle here