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

    Modelling locations on a site

    I would be grateful for some help with this. I have a servicing company that installs equipment on clients' sites. The company wants to know where a particular piece of equipment is located, what equipment is installed at a particular location on a site, where equipment of a certain type is located, and so on.

    Each site is different, so for one the location address of the equipment might be SiteNo 1234, Building A, Sector 2, Floor Gnd, Area Workshop, Location Mens' Washroom, whereas another might be SiteNo 5678, Ladies' Washroom.

    I had thought about a recursive Location entity so that there could be n levels, but Location is weak. Providing a 'chain' of entities from Site, Building etc. to Location runs the risk in use of creating more than one entry for the same location. It would also need to be packed with spurious data where a location is similar to the second example above.

    Does anybody have an idea for a more elegant solution than the 'chain'?

    Any help would be very gratefully received.

  2. #2
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    I do not see any problems with using a recursive solution.


    In fact, when the depth of the hierarchy is variable, a recursive model is the only one that will work.

    You'll have to create two entities: a location entity and an entity showing relationships between locations.

    For example:
    • location(id, name, description, ...)
    • location_hierarchy(location_id, sub_location_id, ....)
    This way, you could have the follwoing data:

    Table Location:
    PHP Code:
     ID Name Description
    ------------------------------------
    1 Site X Site X
    2 Bldg A Bldg A of Site X
    3 Floor 3 Floor 3 of Building A 
    4 Site Y Site Y
    5 Ladies Washroom Ladies room of Site Y 
    Table location_hierarchy
    PHP Code:
    Location_ID Sub_Location_id
    -------------------------------
    1                 2
    2                 3
    4                 5 
    That way, you have all the information you need. A tree walking algorithm can be used to find the full particulars of any location.

    Hope that helps.
    Ravi

  3. #3
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Don't forget to throw a hierarchy level attribute in to help tree walking.

  4. #4
    Join Date
    Mar 2002
    Location
    Cornwall, UK
    Posts
    18
    Thanks for the reply, rajiravi. I've just noticed that I refered to Location as being weak without explaining that Site is a strong entity that would be in a mandatory 1.M relationship with Location. So, Location would inherit Site's PK as part of its composite key - Location(SiteCode (PK), LocationID (PK), ...) I can't model Site as a subtype of Location because of functional requirements. I dismissed the recursive entity because of the composite key. Am I wrong?

    certus, can you give me an example of what you mean?

  5. #5
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    You can build a recursive structure using a composite key, too. Either create a surrogate key (numeric sequence driven), or use the columns of the composite key itself.


    If you want to track the "level" of the tree, then you should add a "level" column to the hierarchy tree.

    I personally do not prefer the "level" column, because it creates more problems than it solves. First of all, you must have an algorithm to calculate the level. Secondly, if the data in the tree changes, (for example, a node with branches is dropped), you have to recompute the "level" value for the whole tree.

    Basic design philosophy suggests that any column that can be derived from other columns should not be stored in the database, unless you have a very good reason for doing so. "Level" being a derived column ought not to be stored directly unless you really, really need it, and performance is poor without the column.

    Ravi

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there are other models for storing hierarchies

    (disclaimer: i don't know if they require recusion but i suspect they do not)

    one such model is described here: http://www.onlamp.com/pub/a/onlamp/2...ql.html?page=1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2002
    Location
    Cornwall, UK
    Posts
    18

    A thank you

    I'm sorry about the delay in thanking you all for your help. It really is a great forum.

Posting Permissions

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