I'm setting up a new database with the following types of orders
Purchase Orders (sent to manufacturer for inventory)
Sales Orders (received from customers)
The manufacturers will get reports of when/where their items are sold but I don't understand how I'll connect the two for reporting purposes
If I order 5 widgets for inventory at different costs over time - and 3 are sold - how do I know which widget went where....
Currently I have a field "SO_ID" on every "PO_ID" - and a "PO_ID" field on every "SO_ID" so when an item is picked and the serial number is captured they sync-up - but it just doesn't seem like the best way to do this. It seems like the way I do it now has repeated data and that's what normalization tries to avoid.