asset tracking tables setup in sql?

asset tracking tables setup in sql?

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: users 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 users houses. I need to know when and where these computers are installed. The technicians also pick up the computers. I need to know when they do this and when the computers are returned to the office and why. I am having trouble setting up my tables correctly. here is what I have so far…
computer_location
computer_location_id pk 1 2 3 4
computer id problem: (solved with the type id they may be equal but their type wont be so they will be dealt with accordingly.)what if user_id and company_id are equal? How do we know which one it is? The type id will tell you if it is a user or a company where the computer is located! 1 2 1 4
location id(users id or company id[ial is a company in the list of companies]) who has pc can= user,company (ial or manufacturer) (ses userid=)2 2 (compid=)1 2
type_id 1 1 2 2

transaction_id 1 1 6 7
transaction_date_time 1/1/2011 1/1/2011 1/15/2011 1/15//2011
status_id 4 4 6 1
notes sent for repair @ office

type_id_name 1 2 3 4
type_id ses user company contact? lead
type_name

status 1 2 3 4 5 6
status_id staging DOA ready deployed retrieve repair KIA
status_type we have it but not ready we have but doesn’t work ready to be installed at eu installed needs pick up and returned to office repair and change status to ready used but died in service must be sent back to manufacturer if under warranty

transaction_type
transaction_type_id 1 2 3 4 5 6 7
transaction_type install repair return pickup change ownersship swap-out(took this computer out) swap-in(installed this pc in)
includes headset, camera, sometime monitor

I need help structuring the tables and I guess the logic of how this works.
thanks

Tags: , , ,

Under Forum

1 Comment for asset tracking tables setup in sql?

  • 1. TheMadProfessor  |  February 15th, 2011 at 8:36 am

    This might be a step in the right direction:

    computer_location (location_id, description)
    1 “user home”
    2 “user office”
    3 “my office – repair”
    4 “my office – employee use”
    5 “manufacturer for repair”
    6 “manufacturer for replacement”
    …

    computer_status (status_id, description)
    1 “DOA”
    2 “ready for user”
    3 “deployed/delivered”
    4 “undergoing analysis”
    5 “repair under way”
    6 “requires manufacturer attention”
    …

    transaction_type (transaction_type, description)
    1 “install”
    2 “repair”
    3 “return”
    4 “pickup”
    …

    computer (computer_id, current_location, current_status, manufacturer, …)

    transaction (transaction_id, computer_id, transaction_date, transaction_type, start_location, end_location, start_status, end_status, previous_transaction_id, notes, …)

Leave a Comment for asset tracking tables setup in sql?

You must be logged in to post a comment.

Trackback this post  |  Subscribe to the comments via RSS Feed

Categories