I'm struggling with table relationships with regards to a Goods In and Out Database
So.
I have Goods that are ordered in. They come with a Clearance Number. I can have multiple Clearance Numbers for a Product.
eg.
ClearanceID
ClearanceNumber
CearanceDate
Then I will categorise the goods in by product - New Table.
GoodsInID - PK
ClearanceID - FK from table above
ProductID - FK from a list of products
NumberGoodsIn
However I need to have goods go out. Now the goods out will need to be from the overall total.
My own confusion is that If I have
GoodsInID - 001
ClearanceID - 001
ProductID - 45
NumberGoodsIn 300
GoodsInID - 002
ClearanceID - 001
ProductID - 12
NumberGoodsIn 400
GoodsInID - 003
ClearanceID - 002
ProductID - 45
NumberGoodsIn 400
What is the best way to link goods out to take away from eg. ProductID 45 NumberGoodsIn 700 ?
I was thinking of GoodsInID as a FK to the table GoodsOut, but in my mind I cant see that this is correct as on the reporting side I may have issues consolidating my aggregate queries etc.
Am I overthinking this?