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.
NumberedRooms
andAncillaryRooms
respectively, both with attributesName
andZone
. Both have a superkey on the compound ofZone
andName
but is a minimal superkey only inAncillaryRooms
,Name
being a minimal superkey inNumberedRooms
. So having them in separate tables is good in terms of constraint enforcement and the common attributes means you canUNION
them to present them as a supertype, perhaps namedRooms
. p.s. I recommend in the strongest terms you avoid nullable columns.NumberedRooms
andAncillaryRooms
, how do I reference them with a foreign key from other tables?