Inventory System Table Design

[szlamany]

It does not make sense to me, in a SQL world, to have a RAW material table and a Order table.

You should have a single inventory table - something like this (and this is just for example purposes):

Product Id
Transaction Date
Transaction Type (C for CREDIT, D for DEBIT)
Transaction Sequence (if needed)
Reason Code (I for initial quantity, R for restock, O for order, A for adjustment)
Quantity

So you would end up with data like this:

ProdId   TransDate    Type  Seq  Reason  Quantity
000001  2004-02-01    C     001     I       50
000002  2004-02-01    C     001     I      100
000001  2004-02-10    D     001     O       20
000001  2004-02-10    C     002     R      100
000002  2004-02-20    D     001     O       99
000001  2004-03-01    C     001     A        2
000002  2004-03-01    D     001     A        1

Some initial stock of product 000001 and 000002 on 2004-02-01.

Then an Order and Restock of product 000001 on 2004-02-10.

Order of product 000002 on 2004-02-20.

Then some stock adjustments on 2004-03-01 - found out we had 2 more of product 000001 and that the remaining quantity of product 000002 was lost/damaged.

This main INVENTORY table drives everything about the quantity of stock - on-hand, ordered, sold - all figures - date driven.

You have additional tables for order info - linked however you want - maybe with product id, date and sequence - only when you have a REASON code of "O" for order.

This respects the "set-based" logic that you are supposed to use in SQL - and loses the old "record-by-record" processing techniques that we should avoid in SQL.