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.
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
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
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_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_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
(1, 'Call the police!'),
(2, 'Totally unaccptable'),
(3, 'Very poor'),
(8, 'Very good'),
(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!