First off, I am kind of new to database design, so please take that into account.
I have a project that I am working on for my work place. It's something to make my life easier. I'm a new parts man at a diesel mechanics shop. They have hundreds of engine parts that are not organized very well. The person who runs the parts dept. Knows where everything is, but we new folks struggle to find things.
So, I decided to work on a database of inventory for the express purpose of storing the location of the parts inventoried to make them easier to find.
I have been wracking my brain (which is very small by the way) to come up with a good db design for the locations of parts, considering that the parts are scattered in many rooms and in different types of containers. Also, many of the parts are very small pieces, like tiny o-ring's, that take up a small amount of space, and are very hard to find, in the containers they are in.
Most of the parts are on/in shelves, hangers, drawers, or bins. Since there are different types of containers, that might have to be located, by different methods I'm not sure how to finish this up.
I've looked at it from different angles and have finally come up with the following.
I will have an entity called Part, and also an entity called Container that represents the container type and then a Location entity.
The parts are located by building, floor, room, and then location. Like I said before, the location can be on a wall/hanger, on a shelf which might be against a wall. A shelf would be part of a section with numbered shelves, and the part would most likely have a position on the particular shelf. A Shelf could also be on an aisle with multiple shelve sections just like I stated above.
Parts in bins could be in shelf sections, that would be against a wall or on an aisle, and be located by column/row positioning. The same goes for the drawers that we use for brass parts, etc.
My real problem is coming up with how to implement the container positioning, with the different container types located in different ways. The following are examples of the data I would need to store in order to find a part:
A part stored on a hanger might be located by:
Part, Building, Floor, Room, Wall, Wall-Zone, Position
or
Part, Building, Floor, Room, Aisle, Aisle-Position.
A part on a shelf might be located like this:
Part, Building, Floor, Room, Aisle, Shelving-Section, Shelf-Number, Position (number position from left)
And a part in a drawer might be located by the following:
Part, Building, Floor, Room, Aisle, Section, Column, Row.
If any one here has a good idea about how to build a structure that would allow fairly easy retrieval of this type of data, I would really appreciate the advice.
Sincerely, Rich