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'?
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?
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.