Schema Definition For Audit-like Asset Tracking (Entity Framework)


I'm having trouble designing the relationships between entities in my schema. The issue is that each Asset (projector,laptop,etc...) can have a change in purpose/location (among other things), and if I wanted to get the most recent of these values which it would currently involve copious joining etc.... To keep track of these changes I have defined a schema like so:

enter image description here

Meaning that an AssetInstance represents a use of the Asset, i.e being loaned out, sent to repair. Each AssetInstance is responsible for recording the location,shelf (if applicable) and purpose of the Asset. However if I use this approach and I want to get the current user of an Asset with AssetID = 1, I would need to join Asset,AssetInstance then sort by Date and select the top one, then join that with User to get their name.

Ideally I would like Asset to keep retain the most recent Purpose,Location,Shelf and User it had associated with it. I realize this could be accomplished by adding these relationships to Asset and then in my code after a new AssetInstance is created for an Asset update the Assets columns appropriately.

To me this seems far from ideal and I'm wondering if there is a better way to do it.

Thanks :)


Answers:


I think you could see the design from another viewpoint
Lets say AssetInstance is a reallocation of shelf,location and purpose.So I prefer calling the table Transaction or Relocation-Transaction.
As shelf,location and purpose are naturally properties of Asset we will have relation of them in asset Table. Last location, shelf and purpose are saved in these foreign keys in asset table.
relocation-transaction table contains the history of relocation, so we will insert a row in that when the asset is re-locating(perhaps even in first place of asset creation).
I think it would be a good idea to have a base table for transactions, may further actions on the asset will be needed to have traced and reported, like asset out of ordering, selling ...
A draft model would be something like this:
enter image description here Hope this helps.