Database Design: Inventory with multilevel packaging

Question

I'm trying to develop a database for my inventory. But I have no idea how to keep track of multilevel packaging.

For example:

I currently have a products and positions table

products

    Id | Name
================
  1013 | Metal
  1014 | Wood

positions

    id | Name
================
   1   | 1-1-1-1
   2   | 1-1-1-2

And my inventory table I was thinking of doing something like this: Let's say I stored 1 box with 1000 Metal and 1 box with 500 Wood at position 1-1-1-1

ItemId | ProductId | Quantity | PositionId
==========================================
   1   |    1013   |   1000   |     1
   2   |    1014   |   500    |     1

So I'll label those two boxes with a barcode 1 and 2 respectively, so if I scan them, I can check this table to see the product and quantity inside them.

But I can also put these 2 boxes (1 and 2) inside another box (let's call it box 3), which would generate a new barcode for it that, if scanned, will show both previous boxes and its items. And store this box 3 in another position

And I can also put this box 3 inside a pallet, generating a new code and so on. So basically I can multilevel package N times.

What is the best table structure to keep track of all of this? Thanks in advance for any help!

Sounds like you are sort of talking about a Bill Of Materials or a BOM when you combine inventory to create another item right? — Eric, Apr 28 '21 at 13:46
I had never heard of BOM. After a quick search I can see that it's pretty much what I need. I'll search for a few database structures for BOM. Thanks! If you know a structure that fits my case, please let me know. — André Scattolin, Apr 28 '21 at 13:58
I added one below, hope it helps! And if you scan a BOM you have a link to all the products and the quantity inside of that product — Eric, Apr 28 '21 at 14:01
Can you give some examples of questions you want to ask of this database? The problem with BOM database structures can be answering questions like "Which pallet has a box of metal with at least 3, but no more than 12, items". — Neville Kuyt, Apr 28 '21 at 15:44
Sure. A few queries I'll have to do: when I search a position, I want to find all the items (level 1) in that position. In this case, if I scan the position with the pallet, I want it to return 1000 metal and 500 wood. Same answer if I search for the pallet's barcode. and if I search for the product_id I want to find all positions it is in. So in this case, the pallet's position. — André Scattolin, Apr 28 '21 at 17:32
My main issue is when updating the quantity. Example: I remove the wood box from inside the box 3 that is inside the pallet. I'll probably have to use recursive cte, that's why I want to know the best way to structure my table — André Scattolin, Apr 28 '21 at 17:34
You could always add a column to the Inventory Table like a quantity that is allocated to. So for your example lets say you have 500 Wood. 100 Wood is in BOX 3, and 200 Wood is in Box 4. So you could add a column QTYAllocatedToBoxes. So you have 200 Wood still not in any boxes, and the new column would have a quantity of 300 in the QTYAllocatedToBoxes. And you could just SUM(Quantity + QTYAllocatedToBoxes) to get Total quantity on hand — Eric, Apr 28 '21 at 18:59
Don't edit answers into questions, post an answer post. [answer] [help] Roll back. — philipxy, Apr 29 '21 at 01:42
For an alternative way to model hierarchies, see https://stackoverflow.com/questions/38801/sql-how-to-store-and-navigate-hierarchies — Neville Kuyt, Apr 29 '21 at 08:23
Ok, I removed my answer from the question and will post as an answer — André Scattolin, Apr 29 '21 at 18:48

score 0 · Answer 1 · answered Apr 28 '21 at 13:59

0

I would add another column to the products table, make it a BIT and maybe call it BOM, BillOfMaterials, or whatever makes sense to you

So your products Table would look like this

Then you could create another table called BillOfMaterials

Quantity is how many of your products are needed to make up your new product. So for this example 2 metal and 1 wood make a pencil.

answered Apr 28 '21 at 13:59

Eric

212
2
15

Since every box I create (with other boxes/packages inside) will generate a new unique ID, I'd have to add it to the products table too right? I'll try to create my own structure from what you posted too, but It definitely helped a lot. Thank you! – André Scattolin Apr 28 '21 at 14:17
Yep treat every box as a separate product, and no problem – Eric Apr 28 '21 at 18:48
Your model helped a lot, I was able to design my own structure from it and it's working fine. I can do all selects and update queries with recursive cte. Thank you very much – André Scattolin Apr 29 '21 at 00:05
glad I could help! – Eric Apr 29 '21 at 13:18

score 0 · Accepted Answer · answered Apr 29 '21 at 18:49

I was able to make a good structure: My products and positions are the same but I created a stock table like:

   id | product_id | amount | parent_id | position_id
=====================================================
   1   |   1013    |  1000  |    4      |     1
   2   |   1013    |  1000  |    4      |     1
   3   |   1014    |  500   |    4      |     1
   4   |   1234    |  NULL  |   NULL    |     1

The 1234 (random id) is a box that contains 2000 metal and 500 wood. I dont save this box in the product table.

When I scan the box with id 3, I perform a recursive cte query:

with recursive bom as (
    select *, 1 as level
    from testing.stock
    where id = '4' #scanned id
    union all
    select c.*, p.level + 1
    from testing.stock c
    join bom p on c.parent_id = p.id
)
select product_id as product, sum(amount), position_id
from bom b
left join testing.product pd on b.product_id = pd.id
where pd.id is not null
group by product_id, position_id

which returns:

sum   | product |  position
2000  | 1013    |      3
500   | 1014    |      3

to get by position I just run a variation of the above query. To perform an update I get the Ids inside that box and run a

update testing.stock set position = '2' where id in (#variation of above query)

I hope this helps someone. This works for N packaging level

Database Design: Inventory with multilevel packaging

2 Answers2