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.
Best Answer
Here is some vanilla SQL:
The
CHECK
constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attributeName
in tableNumberedRooms
represents an integer.The idea that every row in the supertype table
Rooms
will have exactly one row in the union ofAncillaryRooms
andNumberedRooms
. 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 keyName
alone, whereasAncillaryRooms
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 validRoomType
values.