I'm hoping this will be an easy one. I'm planning on building an application that tracks the physical location of objects. So far, I'm thinking that i can create a table called containers and allow the user to create the containers. Then associate the object with that container. Where it is getting a little tricky for me is that some containers will be inside other containers. I'm considering everything a container. A building, floor, room, cabinet, draw, etc. I want my user to be able to create all these elements as they see fit. The first container they will create will be the building. The user can then create sub containers if they are needed. In some instances the object might be stored in building_a/first_floor/room_b/cabinet_c/drawer_3/slot_2 (6 containers). In other cases it might just be building_a/basement/bin_v (3 containers). Only the top level container will be linked to an address table. All sub containers would be assumed to be at the same location that it's parent container is in so I should be able to write sql to get that info. I made a quickie erd to try and show what I'm thinking (please ignore the naming).
Does this seem to be a good approach?
Should I Create a separate table for the buildings?
Any input would be greatly appreciated.