Database model for a hotel

Question

I have to create a Hotel database according to these requirements:

• information about guests should be found, information about rooms divided into categories, reservations (ordered by a type of a room and assigned for a specific type of a room).

• the following functionality should be provided: make a reservation by assigning a free room with the guests expectations - if you cannot meet guest's expectations, you have to save booking order to be settled by authorized employees

• check how many times given person was a guest in last 2 years

• which guests came to the hotel, accordingly to the reservation

• free the room and write the bill

My code:

CREATE TABLE client_reservation (
    client_id        INTEGER NOT NULL,
    reservation_id   INTEGER NOT NULL
);

ALTER TABLE client_reservation ADD CONSTRAINT client_reservation_pk PRIMARY KEY ( client_id,
reservation_id );

CREATE TABLE clients (
    client_id      INTEGER NOT NULL,
    first_name     VARCHAR2(50) NOT NULL,
    last_name      VARCHAR2(100) NOT NULL,
    street         VARCHAR2(50),
    house_number   VARCHAR2(6),
    town           VARCHAR2(100),
    postal_code    VARCHAR2(6),
    telephone      VARCHAR2(15) NOT NULL
);

ALTER TABLE clients ADD CONSTRAINT clients_pk PRIMARY KEY ( client_id );

CREATE TABLE reservations (
    reservation_id        INTEGER NOT NULL,
    date_of_purchase      DATE,
    beggining             DATE,
    ending                DATE,
    status                VARCHAR2(10) NOT NULL,
    date_of_reservation   DATE NOT NULL
);

ALTER TABLE reservations ADD CONSTRAINT reservation_pk PRIMARY KEY ( reservation_id );

CREATE TABLE resevation_rooms (
    reservation_id   INTEGER NOT NULL,
    room_id          INTEGER NOT NULL
);

ALTER TABLE resevation_rooms ADD CONSTRAINT resevation_rooms_pk PRIMARY KEY ( reservation_id,
room_id );

CREATE TABLE room_category (
    category_id   INTEGER NOT NULL,
    name          VARCHAR2(50) NOT NULL,
    description   VARCHAR2(200)
);

ALTER TABLE room_category ADD CONSTRAINT room_category_pk PRIMARY KEY ( category_id );

CREATE TABLE rooms (
    room_id         INTEGER NOT NULL,
    capacity        INTEGER NOT NULL,
    price_per_day   NUMBER(10) NOT NULL,
    room_category   INTEGER NOT NULL
);

ALTER TABLE rooms ADD CONSTRAINT room_pk PRIMARY KEY ( room_id );

ALTER TABLE client_reservation
    ADD CONSTRAINT client_fk FOREIGN KEY ( client_id )
        REFERENCES clients ( client_id );

ALTER TABLE client_reservation
    ADD CONSTRAINT reservation_fk FOREIGN KEY ( reservation_id )
        REFERENCES reservations ( reservation_id );

ALTER TABLE resevation_rooms
    ADD CONSTRAINT reservation_rooms_fk FOREIGN KEY ( reservation_id )
        REFERENCES reservations ( reservation_id );

ALTER TABLE rooms
    ADD CONSTRAINT room_category_fk FOREIGN KEY ( room_category )
        REFERENCES room_category ( category_id );

ALTER TABLE resevation_rooms
    ADD CONSTRAINT room_reservation_fk FOREIGN KEY ( room_id )
        REFERENCES rooms ( room_id );

Model:

Thanks for every suggestions, cause I'm not so sure if I meet requirements and put right relations (identifying or nonidentifying)

Answer 1

I think, you don't need tables client_reservation and reservation_rooms. You have tables clients and rooms, and then, to make a reservation, you need only one table - reservation (I prefer to give table names in singular form). This table will reference to a client and to a room, and the reservation period (start date/end date). Also, you forgot about the actual state of the room - it could be just booked or busy. I would propose to create a new table - status, and reference it from reservation. The table status could contain the following values:

• booked - when a client booked the room
• busy - when a client came to the hotel
• canceled - when a client didn't come at time and room became available for other guests
• unavailable - for example, when a room needs a renovation and/or repair of equipment
• etc.

Also, a billing is mentioned in the task. It is a very wide area, to fully support it, you may need a lot of tables to store the data about billing plans (a room can have different prices depending on a season, a category, available equipment, a client type, a view from the window, the number of the floor, etc.).