Matrix Inventory Items

[nickperez22]

I have several inventory items such as Tshirts that have various sizes and colors. Instead of creating a separate item code for each one, I would like to create only one item, identify it as a matrix item and be able to track the quantity on hand by size and color. I know a lot of programs such as point of sale programs that do this, but I'm stumped as to how this is achieved and the underlying table structures that make this possible. If anyone has an idea of how this is done I'd appreciate it.

 

[MajP]

tblItems
itemCode (primary key)
itemDescription
other fields unique to an item

tblSizes
sizeID (primary key)
sizeDescLong (small, medium, large)
sizeDescShort (sm, md, lrg)
other fields unique to a size

tblColors
colorID primaryKey
colorDescLong (Red, Black,...)
colorDescShort (Rd, Blk,....
other fields unique to a color if any

jncTblInventory
inventoryID (primary key)
itemCode_fk (foreign key)
sizeID_fk
colorID_fk
quantity

so if you have items
abc Hoodie
def Champion t-shirt
Sizes
1 Small Sm
2 Large lg

your colors
1 Blue
2 Red
3 Green

your junction table would look like:
1 abc 1 3 45
2 abc 2 2 37
3 cde 1 1 22

This says that I have 45, small, green hoodies
and
37, large, red hoodies
and
22, large, blue, t-shirts.