1

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?

Lou
  • 2,200
  • 2
  • 33
  • 66
  • 1
    I've edited the question in an attempt to make it less "Too Broad", I would appreciate suggestions for further improvement in place of further close votes. – Lou May 10 '19 at 21:30
  • "Recursive" is a property of definitions/algorithms/representations, not of functions/relations. [What are the options for storing hierarchical data in a relational database?](https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database) [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – philipxy May 10 '19 at 22:35
  • What exactly is "this design"? PS The obvious design, which apparently is your design, is 'containter item [c] contains item [i]', 'item [i] has description [d]', 'item [i] has property [p]'. Re "my design doesn't neatly accommodate sub-containers" Why do you think that? You already said "Each Item is linked to its Container by the ContainerID" & "a container can be both a literal container or simply one item". So your design does "neatly accommodate sub-containers". – philipxy May 11 '19 at 01:35
  • Re "good" & "I'm not sure if that is considered bad practice in database design": What do we answer? We must rewrite a textbook. Time to read a published academic textbook on information modeling & database design. (Manuals for languages & tools to record & use designs are not textbooks on doing information modeling & database design.) PS "data redundancy" (like "efficient" & "good" & "bad") doesn't mean anything in particular & anyway only "bad" "data redundancy" is not "good". – philipxy May 11 '19 at 01:45

1 Answers1

2

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:

enter image description here

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:

enter image description here

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

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
  • That's a really good idea, thanks! I'm a little bit confused about how this would be implemented although I think I understand the theory - are you suggesting that each `Item` has both a `ContainerIDFK` and a `ContainerID` property, where the first one refers to another item's `ContainerID`, so that items are essentially linked to each other? – Lou May 10 '19 at 21:58
  • 1
    Not quite - I'm saying that each record in the `Items` table would have a unique `ItemID` PK, and that the `Container` field would contain the `ItemID` of another item in the table. Similar to the setup described in the question linked in my answer. – Lee Mac May 10 '19 at 22:03
  • Ah I see, so for a box containing a bag which contains a widget, the box has an entry in the `Container` table, the bag has an entry in the `Item` table with the `ContainerID` of the box, and the widget has an entry in the `Item` table with the `ContainerID` of the bag. – Lou May 10 '19 at 22:06
  • 1
    I think you've got it, but there would only be the `Items` table, with no `Container` table - I've added an example to my answer to help explain. Of course, this is but one way to approach this. – Lee Mac May 10 '19 at 22:18
  • Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. – philipxy May 10 '19 at 22:33
  • Thanks @Lee Mac, I'm with you now. Would it be possible to link the ContainerID to the ItemID without VBA though? – Lou May 11 '19 at 11:49
  • 1
    @Lou, Yes: you can use a self-join in a query - the linked answer only uses VBA to construct the SQL query with sufficient self-joins to traverse all levels of the hierarchy. – Lee Mac May 11 '19 at 12:54