Seek database design guidance

[gol4]

I have a question on Database design. Lets assume I want to track equipment location information. The location can be at various buildings. Those buildings can have various floors that hold various departments that are located in various rooms Etc…
Currently I do something like this;
TblBuilding
BuildingID Buildingname
1 Building1
2 Building2
DepartmentTBL
DeparmentID Departmentname
1 Operations
FloorTBL
FloorID FloorName
1 2nd floor
Roomtbl
RoomId Roomname
1 exam12
Equipmenttbl
Equipid BuildingID departmentID floorID RoomID
123456 1 1 1 1
The problem with the above is that equipment location information can require a differing hierarchy then allowed by the above design.

The following design allows for unlimited hierarchy and appears more efficient but is not nearly as practical.

LocationTBL
LocationID Location ParentLocationID
1 Building1 0
2 2nd floor 1
3 Operations 2
4 Exam12 3
5 Building2 0

EquipmentTBl
EquipID LocationID
123456 4

You can see that the equipment is located in exam12, operations, 2nd floor , Building1
To query all of the equipment located in Building1 becomes a major undertaking as I can only get it by recursing thru the locationTbl .
Is it better to stay with the first design or to spend hours writing functions that can extrapolate the data from the later design. Or better still is there a happy median I am overlooking

 

[ToeShot]

I think you should re-design your location table to something like this

LocationID BDGName DPTName FLRName RMName Equipment

have LocationID as an AutoNumber field and have the other fields as LookUpfields to the other tables. So basically what you would get is when you wanted to track a new piece of equipment you would enter a new record then use the drop down boxes to select the location of the equipment then you would have that record stored with the location that could be easily be query for buildings, floors, departments etc..

HTH

If you need some help let me KNow.

Email: ToeShot@Hotmail.com

 

[braindead2]

Toeshot,
I appreciate the feeback but I don't see how that will help me with differing hierarchy. In the example I provided I used building,department,floor,room. but in practice what will happpen is I may need a fifth or more descriptor to describe the location accurately
examples:
building1,3rdfloor,accounting,corridor,ceiling,grid7
building2,parkinglot3,grounds,stairtower,level3.
Yes I could add additional fields to the table but since the majority of the equipment doesn't require this level I would end up with null fields(poor design)
For a graphical view and ease of use I like to display hierarchy in a treeview. Using the locationtbl and VBA, I can recurse thru and fill the children nodes with very little code and to an endless level
Example:

+Building1
+1stfloor
+operations
exam1
exam2
exam3
accounting
receiving
building2
building3

But my real problem I don't know how to write a recursive Query that allows me to utialize the "one" table, so I often end up with tables holding redundant data (Poor design)
This same sort of problem can happen with a database that keeps track of families or inventory tracking.
I am wondering how others handle it. Thanks

 

[ToeShot]

here is what i was trying to get across you have your five tables building, department, floor, room, equipment and all these tables will hold would be there repective names nothing more. then you would have a sixth table in that table you would have an unique locationID to give you distinct locations in the other fields they would lookup the other key information to the location of the equiptment. for example
first record in th sixth table could look like this.

LocationID 1
BDGName Building A
DPTName Purchasing
FLRName 5th Floor
RMName Video
EquipmentID 123456

This would be a single record saved to a table then you would create another record in the same manner. If you need more description I would add a memo field to the Sixth table

Now from this table you would create a report in this report you would create groupings With building, Department, Floor, Room
This would give you what you are looking for.
There would be no redundancey in the data since the sixth table is actually looking up its data from the other five and not actually storing it.

Hope this is clearer. Note you may need to create a query for the report to get the names in the five tables depending on how you set your lookup up and the five tables. I suggest just the names and nothing else

 

[gol4]

Hotshot,
Again, the feedback is deeply appreciated, however I don't see how what you are suggesting is any different from the way I originally was designing it except to add an additional index (locationId) equipmentid should be enough of an index.

MY original
Equipid 123456
BuildingID 1
departmentID 1
floorID 1
RoomID 1

Your suggestion
LocationID 1
BDGName Building A
DPTName Purchasing
FLRName 5th Floor
RMName Video
EquipmentID 123456
Locationid would only come in handy if I was tracking multipule locations, which I am not.

Both of the above designs requires multiple tables. What I was hoping to achieve would require only one.
An example to help you see where my train of thought is.
we can eliminate 4 of the tables by this design

Locationid locationtype location
1 building building1
2 building building2
3 department accounting
4 floor 2ndfloor
5 room exam1
then do a drop down for selection with "select * where locationtype= 'floor'"
now buildingtbl,departmenttbl,floortbl and roomtbl are obsolete
I can store this data in a table designed like this
Equipmentid,building,department,floor,room and rather it holds the locationid or location is irrelevant.
The problem with the above is data entry mistakes can and do happen , I can select departments for buildings that don't exist, wrong floors wrong rooms etc.. as well as there may not be enough fields to hold the varying levels of hierarchy I desire
The point I am hoping to achieve is what I will call the next step.
If I break it down each piece is located in an area that is located in a higher area.
With that in mind the following design just seems to make sense to me

Locationid location parentlocationid
1 building1 0
2 2ndfloor 1
3 accounting 2

Now I have unlimited hierarchy as well as can now limit the selection
I drop locationtype because in the hierarchy It no longer seem to matter. I can allow the users to drill down to the final location.
How to store this data is where I stumble.
In this design
Assetid bldg floor dept room
I loose the abilty to store a greater heirachy. It seems to me like the best way is to hold the final leaf on the tree and follow it back, I could store node.fullpath but it just seems to me there should be a way in SQL to recurse. I hope this is not more confusing and your thoughts are appreciated.