I am perplexed on how to best design something. For sake of simplicity let's assume it is an inventory item since that's universally understood.
Let's say I'm designing a tracking for Diamonds. Each Diamond is assigned a unique ID or serial number.
So in a table I would create say one table for Inventory Descriptions to limit or control what class each Diamond is put in to. Then another table for where I would assign every Diamond it's Serial Number and select the Inv Description.
Now then, let's say I have Diamond serial number ABC123XYZ. Diamond's often are split or re cut. So then, I'd like to have a function where Diamond ABC123XYZ becomes
However, when I start pulling history reports I would like to see the trail from the first to the end.
If I have one table for Diamond Serial number how do I accomplish that????????
Do I setup another table for that historical detail ???? I'm having a mental block on how it could be accomplished.
By the way.....it's really not Diamonds......