Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Location
    Cornwall, UK
    Posts
    18

    Question Unanswered: Recursion and using trees in Access

    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.

    I would be very grateful for any help with this.

    Philip

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    sounds like you're just having a little trouble understanding how installation sites relate to your overall schema. I would avoid the recursive route like the plague, it's not needed here anyways.

    You described all of the entities perfectly, those should all be in their own table.

    Each site has one or more buildings
    This one is pretty straight-forward:

    tblSite
    ----------
    site_id

    tblBuildings
    -----------
    building_id
    site_id

    each building may have one or more building parts
    right-o...

    tblBuildingParts
    -----------
    building_part_id
    building_id

    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.

    tblArea
    ---------
    area_id
    building_id
    description

    Each floor or area may have one or more locations where the equipment is installed.
    Ah, so THIS is where locations get attached...

    tblInstallLocations
    -------------
    install_location_id
    building_part_id
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2002
    Location
    Cornwall, UK
    Posts
    18
    Thanks for that, Teddy. It's pretty much how the model started out before I got seduced :-) but there are still a couple of worries I have. I'll think about these and post later.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •