Set up for creating a "family tree"
I'm not looking to create an actual family tree, it is just the closets example of what I am seeking.
I have an application that collects information regarding the various parcels of land our company owns/manages. From time to time, these parcels are split and/or merged with other parcels to create new ones. Those new ones may again be split/merged to create yet another set of new ones. I'm looking for a way to somehow connect the dots between all these splits/merges so that if I look at a resulting parcel it will show me all the previous parcel records it used to be.
My current set up is this.
Parcels table - one record per parcel number which is unique.
Transactions table - one record for each action (buy, sell, split/merge).
T2P table - acts as the many-to-many intermediary table since a single transaction could apply to multiple parcels.
Example scenario:
Parcel A is split into B & C. All 3 are entered into the Parcels table as separate records. A transaction record is created in which A, B, & C are added as child record.
Later Parcel B is further split into D & E. D & E are entered as new parcels, a transaction is recorded linking B, D & E.
Goal: when viewing parcel E I would like to see that not only did it come from B but it originated from parcel A. Conversely, when viewing A, I would like to see that it changed not only into B & C, but also D & E.
Currently I have been achieving this by including all previous parcels as children when creating the new split transaction, and also going back to previous transactions to add the new children. Just wondering if there was a different route to achieve this that wouldn't rely on me having to go back and edit previous records.
Thanks
------------------------------
Ember
------------------------------