asset tracking database design help?

asset tracking database design help?

I need to set up a asset tracking db. I have to track where computers are by location. I have several locations that a computer can be located: user’s house, my office for repair, my office for employees to use, manufacturer for repair, returned to manufacturer if it dead on arrival. I have technicians that install the computers at user’s houses. I need to know when and where these computers are.
The part I am very confused about is how to design the tables for tracking the computer location and status with date time stamps.
I need to know when and where a technician installs a computer, picks up a computer, swaps out a piece of hardware or tower, has them in their car, then when they get back to the office the equipment is received. once at the office it is received and diagnosed then it is repaired, sent back to the vendor/manufacturer(if under warranty), sold, used for parts, or disposed of.
each computer can have several users at one address or location. They also can be at many addresses and locations.
Conversely, a user can have many computers over a period of time. Not so much having more than one computer at one time. Users can also have many addresses over time. Not at the same time though.

I have been struggling with this for days now. I can’t seem to wrap my mind around it! Please help!!!!
thanks

Tags: , , , ,

Under Forum

2 Comments for asset tracking database design help?

  • 1. pearpcs  |  February 15th, 2011 at 8:25 am

    Just think of it as a regular table in Excel, and then make your database that way. Should get you started.

  • 2. Ratchetr  |  February 15th, 2011 at 9:18 am

    Off the top of my head (am sure I would make some changes over time):

    You obviously need an Asset table. It definitely needs a primary key. You would have other obvious stuff like asset #, date purchased, yada, yada… I would probably also have a column for current location, and a column for current status (In service, In Transit, Out for Repair, Sold, Retired, etc).

    You need a table for locations. Also needs a primary key, as well as your typical location stuff like address. The current location column in Asset is a foreign key to this table. An asset can only ever be at 1 location at a time, so it’s a 1 to 1 relationship.

    You should have an audit table that records each time an asset is moved. It would have an asset id (foreign key to Asset table), old location (foreign key to location table), new location (foreign key to location table), a timestamp, and probably something to record the who and the why for the change.

    You probably also want a users table. But it’s less clear what you want to do here. I don’t know if you would want to map users to locations, possibly. Users to computers maybe?
    If you want that, add a users table with a primary key and the usual user stuff.

    Create a mapping table, say UserLocations. It would have user id and location id. You can assign a user to multiple locations this way, and you can assign many users to a single location.

    Handling assets in transit may be a bit tricky. Maybe Bob’s car is a location, so when a tech removes a machine and puts it in his car, you can track that it’s there. But what about Fedex or UPS? Those could be pseudo-locations as well, I suppose. Or you set the location field in Asset to NULL and record in transit information in a different sort of table. Not sure what is best here.

Leave a Comment for asset tracking database design help?

You must be logged in to post a comment.

Trackback this post  |  Subscribe to the comments via RSS Feed

Categories