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:
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
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.
I think you could see the design from another viewpoint
is a reallocation of shelf,location and purpose.So I prefer calling the table
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:
Hope this helps.