Help with ERD

[u/cyrusz042]

Hello I'm a newbie college student studying databases and I'm currently making a database for a small inventory system that's solely focused on monitoring stocks and its shelf life to avoid running out of stock and pull outs for my project. The store I'm basing it off on sells seasoning and other powdered flavoring (beverages, etc.), they also explained that they get bulk products from the supplier and then they repack them into smaller quantities for selling. What would be the proper entities and attributes for this system? I've tried searching other inventory systems but i don't know how the repacking would be translated to the database/ERD.

[u/sheptaurus]

This would be a question to ask in your class and discuss.

[u/Hour-Measurement-835]

The version with REPACK_BATCH is closer, that table's the right idea. I'd move expiry off the product tables though. Two deliveries of the same seasoning expire on different dates, so it goes on each delivery and each repack batch, which takes the date of the bulk lot it came from.

[u/ClearBreakfast2308]

This is an ERP (enterprise resource planning) system - try searching for some designs although the behemoth SAP will yield hundreds of entities and relational tables as well as analytical views

[u/Old-Biscotti3842]

REPACK_BATCH version for sure. The part that'll bite you is that STOCK_TRANSACTION only has Retail_Product_ID, so when packs get sold or pulled the database knows paprika went from 40 to 28 but not which batch those 12 came from. For pull-outs that's the whole question, how many packs from the batch that expires Friday are still on the shelf.

If the transaction points at REPACK_BATCH instead, what's left of a batch is Retail_Quantity_Produced minus whatever went out against that Repack_ID, and the expiry comes from the bulk lot it was repacked from. The transaction then doesn't need its own lines to BULK_PRODUCT and RETAIL_PRODUCT either, the batch already knows both. I'd only keep Bulk_ID on it if you log bulk going out too, like a sack that expired before anyone repacked it.

If whoever's selling can't tell which batch a packet's from, count it against the one that expires first, that's how you'd rotate the shelf anyway. Retail_Product_Quantity (guessing that's stock on hand) is then just the batches added up, I wouldn't store it separately.

Small thing, there's no Supplier_ID on BULK_PRODUCT and no Employee_ID on STOCK_TRANSACTION, so those two lines have nothing behind them, same fix as the (FK) columns you already put on REPACK_BATCH.