Product/asset tracking database with history per item

[Fred F]

Hey,


I'm having trouble designing a database to track our product by location and its various components. The actual relational database is no trouble, ER diagram is very simple - in this example for simplicity I'll use a Computer as the product. Each Computer, referred to by serial number can be in one Location (eg customer's office, our workshop, our warehouse, sub distributor etc) and each location can have various Computers. Each Location can also have various Monitors. Each Computer can also have various Dongles, referred to by Dongle number.

This is fairly easy to set up and will allow us to see the current location of any stock, along with the Dongles present on each Computer. My issue however is that we need to be able to track changes and pull up a history for each Location (which kit has been there), Computer (which locations it has been in, repairs made, etc), Dongle (previous computers and locations). During a warranty replacement situation, a computer in a certain Location can be replaced with new stock, at which point the old Computer will be returned to our workshop and repaired. Original dongles are to be assigned to the new Computer. In some circumstances Dongles can be returned to our warehouse when not required and these can be assigned to either stock or sent out to a new customer.

This is where I struggle. I've got a lovely ER diagram here but just can't imagine any sensible way of tracking the above. Any suggestions, tutorials, advice, pointers to details of a whole new way of database design which I've never heard of before and will solve all my problems...all would be hugely appreciated :)



Edited 2 time(s). Last edit at 12/01/2010 10:22AM by Fred F.