1

In an example scenario for a database design assignment, a company provides hotel rooms (split into two entities - Hotel and Room) and apartments. So the database will contain the following entities:

Hotel (hotelNo(PK), name, address, telephone)

Room (roomNo(PK), hotelNo(PK), dailyRentalRate)

Apartment (apartmentNo(PK), address, numberOfRooms, dailyRentalRate)

In the specifications the company wants the database to contain an inspection entity which stores the following data:

  • Date of inspection,
  • The condition of the room/apartment,
  • The staff member who did the inspection.
  • The hotel and room number and apartment numbers depending on whether the inspection was on a hotel room or apartment.

My question is would it be better to split this up into two entities as shown below or to have one entity (also shown below) which would have a null value for either the hotel and room number (when the inspection is for an apartment), or the apartment number (when the inspection is for a hotel room).

Two separate entities:

HotelInspection (hotelInspectionNo, date, condition, staffNo(FK), roomNo(FK), hotelNo(FK))

ApartmentInspection (apartmentInspectionNo, date, Condition, staffNo(FK), apartmentNo(FK))

One entity:

Inspection (inspectionNo, date, condition, staffNo(FK), roomNo(FK), hotelNo(FK), apartmentNo(FK))
2

If I were you, I would do something like the following:

(tl;dr) - you should have one inspection entity - they are a hair's breadth apart and in this context, essentially one and the same thing! Modifying your schema slightly makes them one and the same.

CREATE TABLE location  -- "master" room/apartment (could be called "accomomation" or similar)
(
  location_id INTEGER PRIMARY_KEY,
  location_type VARCHAR (10) NOT NULL,
  -- CHECK accommodation_type IN ('Hotel', 'Appartment'), could also have 'Hostel', 'AirBNB', 'Tent'...
  -- MySQL doesn't support `CHECK` constraints - use a lookup table!
  location_name VARCHAR (25), -- colloquial name
  location_phone VARCHAR (20) -- either the switch or owner's mobile/cell.
);

Note:

  • all lower case for table names - easy to read: SQL in caps, identifers in lower with underscores.

  • always (depending on your RDBMS - though all the major ones comply these days), give your PRIMARY KEYs, FOREIGN KEYs and UNIQUE INDEXes (i.e. all database objects) meaningful names - makes debugging much easier.

  • you will notice that I put in long-ish names for my fields - this is because

    • a) easier to debug and,

    • b) code/programmes spend 99% of their time in maintenance - typing a few extra characters is a small price to pay for an easier life!

  • Never use keywords (e.g. DATE) as table or column names! Makes life hell with quoted identifiers &c.

  • always try to reduce (within reason) the number of points in your schema where NULLs can be entered. It makes logic easier and can help the optimiser! Most of my column definitions can be followed with NOT NULL below!

  • you might also want to check out this site for other ideas. My thoughts are only a "first pass".


CREATE TABLE hotel
(
  hotel_id INTEGER PRIMARY KEY,
  hotel_name VARCHAR (50) NOT NULL,  -- full business name
  hotel_address INTEGER, -- FK reference to address table?
);

CREATE TABLE room
(
  room_id INTEGER PRIMARY KEY, -- as opposed to room_no - room numbers in hotels can change, renovations, buidling works!
  location_id INTEGER, -- FK to location. Also `UNIQUE INDEX` to prevent dups.
  hotel_id INTEGER, -- FK to hotel
  room_no INTEGER,
  room_rate INTEGER -- FLOAT/DECIMAL/whatever
);

CREATE TABLE apartment
(
  apartment_id INTEGER PRIMARY KEY
  location_id INTEGER, -- FK to location! Also, `UNIQUE INDEX` to prevent dups!
  apartment_address INTEGER, -- FK to address table
  apartment_room_count INTEGER,
  apartment_rate INTEGER -- FLOAT, DECIMAL...
);

CREATE TABLE location_rating
(
  location_rating_id INTEGER NOT NULL PRIMARY KEY,  -- if possible give your PK a meaningful name.
  accomodation_rating_text VARCHAR (20) NOT NULL
);

INSERT INTO accomodation_rating
VALUES
(1, 'Call the police!'),
(2, 'Totally unaccptable'),
(3, 'Very poor'),
(4, 'Poor'),
(5, 'Acceptable'),
(6, 'Reasonable'),
(7, 'Good'),
(8, 'Very good'),
(9, 'Excellent'),
(10, 'Kiss the cleaners!');


CREATE TABLE inspection
(
  inspection_id INTEGER PRIMARY KEY, 
  accommodation_id  INTEGER, -- FK to accomodation
  inspection_date DATE, -- more meaningful than just date - anyway, NEVER call a table or field by a KEYWORD!
  inspection_rating INTEGER, -- FK to your accomodation_rating table - NEVER allow people to write free text if possible!

  -- NOW, no need for hotelNo or apartmentNo - plus no NULLs!
);
  • I don't see how this design will allow for inspecting either hotel rooms or apartments. It seems to allow only inspecting hotel rooms. – ypercubeᵀᴹ Oct 26 '17 at 8:56
  • Oops - forgot accommodation_id in the apartment table. Thanks for pointing that out! Edited. – Vérace Oct 26 '17 at 9:03
  • That is better, yes. Shouldn't also be a UNIQUE constraint on (accommodation_id) - on both room and apartment? – ypercubeᵀᴹ Oct 26 '17 at 9:26
  • Done! I kinda left the constraints to the OP - especially since they didn't mention their RDBMS. – Vérace Oct 26 '17 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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