I am working on a database for a washroom service provider and am having difficulties implementing locations for the installation of equipment. Each Site has one or more PlacesOnSite, a recursive entity. Each site has one or more buildings, and each building may have one or more building parts. Each building or building part has one or more floors and each floor may have one or more areas. Each floor or area may have one or more locations where the equipment is installed.
I have read the various Celko posts and am just beginning to understand the approach but I am not at all sure how to implement the above, assuming it is the best way of dealing with the problem. Once a site is set up, there will be very little change to the locations. However, the user must be able to limit the location list to, e.g. those locations on the 2nd floor of the main building, or those locations in the reception area of the mezzanine floor in the west wing of the main building without seeing a list of all locations, which could be very long for some sites.
I had intended to use a series of linked combo boxes so that the lists could be limited progressively as selections are made, but there are possible nulls for building parts and areas that would seem to preclude this, so I am finding it increasingly difficult to determine the best (or any!) approach.
Each building or building part has one or more floors and each floor may have one or more areas
This could be a little tricky. What differentiates a building part from a building area? I'll assume an area is an arbitrary designation such as "wing" or "department", in which case it will be an optional foriegn key when we get down to installation locations. You could create another table for floors too, it would be advisable for "best practices", but you might be able to sneak by without it.
Each floor or area may have one or more locations where the equipment is installed.
Ah, so THIS is where locations get attached...
area_id 'optional, can be null
That building part/area thing is a little hazy, so that's the best advice I can give with current information. Hopefully that points you in the right direction.