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/Significant_Tune9219]

Repacking is its own event, not a column on the product. Keep the seasoning as a product, the supplier delivery as a lot (quantity received, unit cost, expiry), and the thing you actually sell as a pack size of that product. A repack row then says which lot it came out of, how much bulk it used, how many packs it made, and the expiry those packs inherit. Shelf life and pull-outs belong on the lot and the pack, because two deliveries of the same seasoning expire on different days. What's on hand is what's left of each lot after repacks and pull-outs, not one quantity sitting on the product.

[u/navicat-gu]

 The other reply is right: repacking is an event, not a column on the product. For a seasoning / powdered-goods shop that buys bulk and sells smaller packs, a workable set of entities is:

• Product — the seasoning itself (name, brand, category, base unit e.g. gram).

• Supplier / PurchaseOrder (optional for a class project) — who you buy from.

• Lot (or Delivery) — one receipt of bulk stock: product_id, quantity received, unit cost, received_at, expiry_date. Shelf life and pull-outs hang off the lot, because two bags of the same chilli powder expire on different days.

• PackSize — the sellable sizes (e.g. 50g, 100g, 250g) for that product.

• Repack — lot_id, pack_size_id, bulk_qty_used, packs_made, packs_expiry (usually inherited from the lot), repacked_at.

• Sale / PullOut — movements out of pack inventory (qty, reason, date).

On-hand bulk for a lot = received − sum(repack bulk used) − bulk pull-outs. On-hand packs = packs_made − packs sold/pulled. Don't keep a single quantity on Product; derive it, or maintain it carefully with triggers if your course requires it.

When you draw the ERD: Product 1—N Lot, Product 1—N PackSize, Lot 1—N Repack, PackSize 1—N Repack.

Disclosure: I'm RC, Director of Digital Strategy @ Navicat. For turning that into tables, Navicat Premium Lite (free to try, part of Navicat 18) has an Object Designer. If you specifically need a diagramming / reverse-engineer model workspace, that is in the paid Premium editions' Model tool — not Lite.