I am building a WebApp which has a list of Assets. I have a peculiar requirement and I am unsure of the best way to store the data in SQL.
My Asset Headers currently have a relation to a branch, and that branch to a multiple entities (see diagram). However I need to be able to work out from a given date where that asset header was and which branch group/other entity it was associated to as this could differ over time.
The most obvious 2 answers to me would be:
- To store a AssetHeaderHistory table with the following columns, AssetHeaderId, BranchId, BranchGroupId, etc., StartDate and EndDate.
- To store a AssetHeaderHistory table with the following columns, AssetHeaderId, TableName, Id, StartDate, and EndDate.
Issues with the above:
Both options remove the hierarchy for branch in initial design when storing data in the AssetHeaderHistory table, although this may be the correct answer, it still seems incorrect to me. For option 2 there is no foreign key constraints on the history table.
The other option I thought of was to store a BranchHistory table of some sort, although initially this sounds like it could get really complex and need too much breakable logic. Also we would need BranchGroupHistory table etc. etc.
Any other smart design structures for this particular issue would be much appreciated.