Is there a more efficient way to store containers of objects in Access?

Question

I'm trying to create a relational database in Access 2016 to keep an inventory of items stored in our office. Most of them are in boxes of some kind, so I've decided that each record should be a Container, which could either be a box, bag or physical container which holds multiple items, or it could simply be a single uncontained item. For example, a printer sitting on a shelf would still be considered a Container, but the type of container would be 'None'. Whereas a box full of bric-a-brac would have the type "Box", and each of their items would be enumerated in a separate table.

Each Container can have more than one Item within - e.g. a box may have a pack of pens, a HDMI cable and a business card holder. All three items would have their own record in the Item table with various properties describing the item (brand, colour, quantity if there is more than one identical item etc.) Each Item is linked to its Container by the ContainerID - the relationship is one-to-many.

The problem I envisage with this design is data redundancy - because a container can be both a literal container or simply one item (e.g. a printer), in the latter case I would have to name the parent Container "Printer", and also name the child Item "Printer". Or I could leave the name field for the Item blank so that only the Container is named, but I'm not sure if that is considered bad practice in database design.

The other problem is that my design doesn't neatly accommodate sub-containers - e.g. if there's a bag inside a bigger box that has other things inside it as well, I'll simply have to provide a descriptive title "Bag containing pens, cables ..." I can't imagine there's any way to make my database recursive so I can't think of any solution for this one. And given the size of the boxes I'm working with I'll encounter this scenario frequently.

So my questions are two:

1) Is there a workaround for the solution I'm trying to implement that allows me to neatly store containers within containers?

2) Is there a more efficient database design for what I'm trying to accomplish?

Answer 1

Your question certainly fulfils many of the close vote available options and is also likely to attract primarily opinion-based answers, as I'm sure that there are many ways to approach this... nevertheless, one possible 'recursive' solution might be the following:

Create an Items table in which each record contains a unique identifier ItemID as the primary key, and various properties of the item (e.g. description, size, colour, value, type etc.), but also includes a foreign key field called ContainerID or Container which could be populated with the ItemID of another item in the Items table itself:

In this way:

• Your printer example is no longer a container, but simply an item with appropriate properties and no redundant superfluous records.
• Many items can share the same ContainerID field value, representing the items constituting your 'bric-a-brac' being contained within the same box.
• Since the ContainerID refers to another item in the Items table, the container may also have a ContainerID value, allowing you to represent an infinite level of nested containers:

This problem is very similar to the problem of representing a management hierarchy (or indeed, any hierarchy), as explored & answered in this question.