Database Design: What is the best structure for storing site offices and locations?

Question

Background

I am creating a database driven web application for the management team at the place I work. Part of this application requires fairly specific locations to be linked with where equipment resides. The RDBMS that will be used is MySQL.

The site has 5 major zones labelled Black, Blue, Gold, Green and Red. Within each zone are several offices, generally referred to as Blue 57, Gold 155 etc. Along with the offices are rooms such as cleaner's cupboards, toilets and kitchens which have no specific number. There are also a handful of larger spaces which do not technically come under a specific zone.

The Problem

My concern here is how best to store all these locations within my database.

I could of course just have a flat table with an ID and Name columns:

LocationID        Name

45                Gold 1
46                Gold 2
47                Gold 3
48                Red Kitchen
49                Blue Toilet First Floor
...

However, I recognise that there is a huge amount of repetition. Plus if the site changes zone layout, names or office numbering (has happened at least once) it would be a nightmare to fix. So my next thought was to put the major zones and spaces in their own table, so I would have:

LocationID        Name                   ZoneID

45                1                      102
46                2                      102
47                3                      102
48                Kitchen                104
49                Toilet First Floor     101

ZoneID     Zone     

100        Black
101        Blue
102        Gold
103        Green
104        Red

This is definitely better, but with certain types of room - toilets, kitchens et al - there is still repetition (no two numbered offices have the same number, i.e. there is no Red 14 and Blue 14).

Is having a small amount of repetition acceptable to keep the tables simple? If I had a third table with stuff like "Kitchen", "Toilet", "Lockers" etc I would need a RoomType foreign key in the Location table. I would then have a situation where some locations have a Name and a null value for the RoomType, or the opposite and have a RoomType value and empty Name value. This doesn't strike me as being ideal.

Is there a way of doing this that avoids too much redundancy but doesn't increase the complexity massively? These tables will mostly be read with updates and inserts rare once the DB is populated.

Answer 1

Here is some vanilla SQL:

CREATE TABLE RoomTypes
(
 RoomType VARCHAR(12) NOT NULL,
 UNIQUE (RoomType)
);

CREATE TABLE Zones
(
 Zone VARCHAR(10) NOT NULL,
 UNIQUE (Zone)
);

CREATE TABLE Rooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 UNIQUE (RoomType, Zone, Name), 
 FOREIGN KEY (RoomType) REFERENCES RoomTypes (RoomType),
 FOREIGN KEY (Zone) REFERENCES Zones (Zone)
);

CREATE TABLE NumberedRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Numbered'),
 UNIQUE (Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

CREATE TABLE AncillaryRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Ancillary'),
 UNIQUE (Zone, Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

The CHECK constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attribute Name in table NumberedRooms represents an integer.

The idea that every row in the supertype table Rooms will have exactly one row in the union of AncillaryRooms and NumberedRooms. This is merely implied e.g. have 'helper' procs to add rows to both super- and subtype tables as a single operation and use triggers to ensure it is done.

Note NumberedRooms has a simple key Name alone, whereas AncillaryRooms has a compound key on (Zone, Name). All three (non-lookup) tables have a key on (RoomType, Zone, Name) throughout, allowing further subtype tables to referencing them and maintain integrity by further testing for valid RoomType values.