0

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.

Requirement:

My Asset Headers currently have a relation to a branch, and that branch to a multiple entities (see diagram). enter image description here 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:

  1. To store a AssetHeaderHistory table with the following columns, AssetHeaderId, BranchId, BranchGroupId, etc., StartDate and EndDate.
  2. 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.

  • What do you mean "remove the hierarchy for branch"? What's TableName in your second option? Do you mean it's not just the AssetHeader-Branch relation that needs to be tracked, but the whole state of the graph at a point in time? – Kayaman Jun 27 at 5:09
  • As in, the Branch has a related BranchGroup, but storing this in options 1 and 2 would make that relation irrelevant from an asset point of view. – andyb952 Jun 27 at 9:28
  • TableName would be either OperatingCentre/Branch/BranchGroup/Division/OperatingCompany – andyb952 Jun 27 at 9:29
1

Don't use option 2. It's always a recipe for disaster when people get the idea "hey, if I store things here as text, I can do all kinds of things and the database can't prevent me from shooting myself in the foot".

So you're trying to store the whole state of the graph at a given time. In that case option 1 would work. While an asset doesn't have a direct relation to e.g. BranchGroup (so you might think "why should AssetHeaderHistory store BranchGroup, it's Branch's responsibility"), it would be an efficient way to store a snapshot of the whole state in a single row. The hierarchy isn't gone, it's just stored in a different way.

Alternative solutions would be more complex, as your design doesn't seem to have foreseen the need to retrieve the state at a specific point in time. Otherwise you would've had a lot more choices to consider, for example row versioning in Branch (although I wouldn't say that's a better idea). Now you have a requirement for Asset which is affecting Branch. Storing an "FK snapshot" in the history table allows you to get what you want without intruding on Branch and other tables.

  • That was also my opinion on option 2. This is a relatively new application so I am open to changing designs to handle this better. I will take a look into row versioning, something I have come across before but didn't even cross my mind! – andyb952 Jun 27 at 10:30
  • It could be useful if your requirements involve also observing the state of the individual entities. The downside is of course more complex logic, etc. I recommend keeping it simple and designing based on your current needs rather than "what if in the future I want X?". – Kayaman Jun 27 at 10:47
  • Thank you for taking your time in answering! – andyb952 Jun 27 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.