Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
It sounds like someone has already classified them as three types: 1) numbered rooms (have a number that is unique across the enterprise), 2) ancillary rooms, 3) larger spaces. We know that numbered rooms and ancillary rooms have a commom attribute ('zone'). I suggest you look for further common attributes between these three types to identify possible supertypes. –  onedaywhen Feb 28 '12 at 14:10
    
Consider two tables for NumberedRooms and AncillaryRooms respectively, both with attributes Name and Zone. Both have a superkey on the compound of Zone and Name but is a minimal superkey only in AncillaryRooms, Name being a minimal superkey in NumberedRooms. So having them in separate tables is good in terms of constraint enforcement and the common attributes means you can UNION them to present them as a supertype, perhaps named Rooms. p.s. I recommend in the strongest terms you avoid nullable columns. –  onedaywhen Feb 28 '12 at 14:18
    
I have asked the question of whether individual ancillary rooms - kitchens etc - have been designated any unique name or number. I hope to hear back soon! In the case of certain other spaces, most, bar a very small few, are in particular zones even if they are not referred to as being there. Those not in a named zone could go into another logical grouping such as "Grounds" or "Car Park" etc. Thanks for your comments so far, it has steered me in a direction to take with this. –  Peter Spain Feb 28 '12 at 16:47
    
Just a quick question, assuming I have the two tables you describe, NumberedRooms and AncillaryRooms, how do I reference them with a foreign key from other tables? –  Peter Spain Feb 29 '12 at 10:41

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Showing my lack of experience here, but with your table structure how would I query for the full location - Zone and its number or name? Similarly if I have a foreign key in an Equipment table do I point it to Rooms? I haven't worked with super/sub-typing so my knowledge is a bit thin (read: absent) in this area. –  Peter Spain Mar 1 '12 at 11:09
    
@PeterSpain: "how would I query for the full location - Zone and its number or name?" -- SELECT * FROM Rooms WHERE Zone = 'Red' AND Name = 'Kitchen'; –  onedaywhen Mar 1 '12 at 13:48
    
"if I have a foreign key in an Equipment table do I point it to Rooms?" -- yes, if there is an association between Equipment and all types of rooms, however if, say, there is only an association between Equipment and AncillaryRooms then the FK should reference AncillaryRooms only. –  onedaywhen Mar 1 '12 at 13:50
    
Some Equipment can be any room - an air conditioning unit, for example, can be found in offices and the comms room. Networking equipment can be found just about anywhere. Thank you for your time oneday, this has been a huge help! :) –  Peter Spain Mar 5 '12 at 10:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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