Results 1 to 2 of 2
  1. #1
    Mynotoar is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    5

    Is there a better way to create a database for recording items contained in boxes?

    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.

    Is there possibly a better solution to accomplish what I'm trying to do? Or at least a work-around to get around the nesting issue? I'd appreciate any input.

    Thanks!


  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    when you don't have clearly defined levels and relationships e.g. shelf, box, bag, item - a shelf holds boxes, boxes holds bags, bags hold items - you need to use a recursive design, It can be done is access but only with the help of using vba. The basic principle would have a table designed along these lines


    tblEverything

    ETPK - PK for this record
    ETDesc
    ETQty
    ETFK - FK to another record in this table

    ETPK....ETDesc...ETQty...ETFK
    1...…….Shelf1.….1...……..0 (0 indicates top level)
    2...…….Box1..…..1...……..1
    3...…….bag1...….1...……..2
    4...…….bag2......1...……..2
    5...…….box2...….1...……..1
    6...…...pens...….3...……..3
    7...…...staples...2...……..2

    etc


    from this you can see shelf1 contains box1 and box2 (which is empty), box 1 contains 2 bags (one of which is empty), pens can be found in bag1 which is in box1, whilst staples are loose in box1

    the recursive element is needed because staples might be loose in a box, in a bag or loose on a shelf - so to find where it is your process is

    find parent to staples - (PK=FK) box1
    find parent to box1 - (PK=FK) shelf1

    or if you wanted to know what was on shelf1

    find children to shelf1 (FK=PK) - box1, box2
    for each box - find children to each box
    for each child in each box - find grand child
    for each grandchild - find greatgrandchild
    etc

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-13-2018, 07:06 PM
  2. Replies: 3
    Last Post: 07-27-2016, 05:47 PM
  3. Replies: 3
    Last Post: 02-22-2014, 11:33 AM
  4. Replies: 2
    Last Post: 06-20-2012, 02:18 AM
  5. Replies: 2
    Last Post: 04-11-2011, 05:05 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums