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

    room_id         INTEGER NOT NULL,
    capacity        INTEGER NOT NULL,
    price_per_day   NUMBER(10) NOT NULL,
    room_category   INTEGER NOT NULL


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

    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: enter image description here

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

  • \$\begingroup\$ The current question title applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review - Asking Questions for guidance on writing good question titles. \$\endgroup\$
    – BCdotWEB
    Commented May 16, 2018 at 11:29

1 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.).


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.