Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2003
    Posts
    225

    Unanswered: Helpdesk - Advice please

    I have been tasked with designing an IT helpdesk database, but to be honest i am not sure what sort of things are usually included in this type of project.

    Any ideas please...

    my company has

    around 400 computers
    8 technicians - can repair machines
    1 help desk clerk - just logs calls.

    the clerk will be logging all calls, problems, time, location of pc etc.

    i need the system to be able to assign a job to the next available technician.

    i am not asking for anybody to design this for me, just guidance in where to start.

    any ideas how big a job this is to do.


    Cheers

    Andy

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244

    Re: Helpdesk - Advice please

    In a very, very small nutshell:

    1. Establish your objectives for the system, i.e. the things it MUST achieve. The smaller the list the better.

    2. Make a list of data that the system will need to store in order to meet the objectives. Equally important is a list of the outputs (reports etc.) that the system will need to produce and therefore the data the system will need to produce these.

    3. You then need to split the list of data - which represents your fields - into separate tables, as per the rules of relational database normalisation. This, often, is the difficult bit.

    4.Create a diagram of how the tables are related

    You're now ready to start the basic construction of the database.

    For more information, see our article about database design at http://www.elmhurstsolutions.com

    I hope this helps and good luck.



    Originally posted by Chimp8471
    I have been tasked with designing an IT helpdesk database, but to be honest i am not sure what sort of things are usually included in this type of project.

    Any ideas please...

    my company has

    around 400 computers
    8 technicians - can repair machines
    1 help desk clerk - just logs calls.

    the clerk will be logging all calls, problems, time, location of pc etc.

    i need the system to be able to assign a job to the next available technician.

    i am not asking for anybody to design this for me, just guidance in where to start.

    any ideas how big a job this is to do.


    Cheers

    Andy
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Mar 2003
    Posts
    225
    ok thanks for your help,

    i have now gathered from various sources a number of tables, that i think i might need to create my helpdesk, some may disagree and i would love to hear suggestions.

    i have attached a list of all my tables, i am just not to sure how i would link them together via the relationships facility.

    i also need to make sure that all my tables are normalized to 3NF.

    can somebody please help me here by just checking my table structures and also help me link them together.

    do you think i should keep a seperate table for things such as:

    priority (High, Medium, Low)
    Type (Hardware, Software, Network, Password,etc)
    Location (Main Office, Lab etc)
    etc..

    These contain just one field....

    then link them via a relationship

    or should i add these as a look up option from with the tables....

    i have now modified the layout of the tables and removed some of the one's not needed ( I think) see at http://www.access-programmers.co.uk/...&postid=238810

    how should i link the helpdesk table to the workstation table, and also not sure how to link the other tables....many to many etc

    cheers

    Andy

  4. #4
    Join Date
    Nov 2003
    Posts
    34
    Each ticket should have:
    Ticket Number (primary key)
    Date/Time Ticket was Opened
    Date/Time Ticket was assigned to worker
    Date/Time started working on Ticket
    Date/Time completed ticket
    Keyword fields (a one or two word field to define the issue - I must stress this/these fields cause it is invaluable to your reporting)

    Priority (seperate table - drop down) (Usually defined as the following
    - 1-Critical Impact (Defined as a user outage)
    - 2-Severe Impact (outage for many users but not all)
    - 3-Moderate Impact (issue can impact almost all users but no outage)
    - 4-Minimal Impact (issue can impact many users)
    - 5-No Impact (Only impacts 1 to a few users)

    Issue Type (Drop down menu - so seperate table)

    Note: The above fields are important because they will give you invaluable information to run graphs and charts to analyze your ticket volumes and consistancies so that you can decrease tickets.

    Issue (usually a free form field)
    Resolution (usually a free form field)
    Caller Information (seperate table)
    - name
    - location (seperate table - drop down menu)
    - contact info
    Assignee (seperate Table - Drop down menu)

    Hope this helps, let me know if you need any advice...I spend a couple of years doing support and using this type of software.
    "If you are good you will be assigned all the work. If you are really good you will get out of it."

  5. #5
    Join Date
    Mar 2003
    Posts
    225
    ok great thanks....i will make some recommended changes, and them post the updated picture of my table layout.

    also regards to the priority......

    is there a set standard to the the time to respond to each priority level....

    eg.

    high - 1 hour
    low - 1week etc

    cheers

    Andy
    Last edited by Chimp8471; 12-09-03 at 07:48.

  6. #6
    Join Date
    Nov 2003
    Posts
    34
    Usually...it's probibly something your team will have to define and it's is mainly based on your user. For example: if you have a Severity 1 and it is causing your users a full outage you should respond to their issue in 15 minutes and have it resolved within 2 hours. But, in some environments your user cannot be out for 2 hours, you have more like 30 minutes. It all depends on who your user is and what their expectations are.

    What I have used:
    Sev 1: Respond 15 minutes, Resolve 2 hours
    Sev 2: Respond 15 minutes, Resolve by end of day (about 6 hours)
    Sev 3: Respond by end of day, Resolve 24 - 48 hours
    Sev 4: Respond 24 hours, Resolve 72 hours
    Sev 5: Respond 24 hours, resolve 3-5 business days.

    Once you've chosen your guidlines, it's best to define them before support begins, document and publish them, and then report on them using the date fields I mentioned in the previous post.

    This way you can not only commend your employees for their response time and customer service but you can prove that your team is an asset to the customer.
    "If you are good you will be assigned all the work. If you are really good you will get out of it."

  7. #7
    Join Date
    Mar 2003
    Posts
    225
    hi thanks for your help yet again, here is my latest picture of the layout of my tables,

    i was wondering if you could check that they are to 3NF, if not could you suggest how i might change them, and is there anything else i need to include.

    i was also wondering if you could check my tables set up in my database attached

    here is the layout of my tables

    http://members.lycos.co.uk/redtilefc/Drawing6.jpg


    and i am still unsure how to link the tables together, this whole one to one , many to many thing confuses me completely

    Picture attached

    cheers

    Andy
    Attached Files Attached Files
    Last edited by Chimp8471; 12-10-03 at 07:12.

  8. #8
    Join Date
    Nov 2003
    Posts
    34
    It's hard for me to tell you your relationships cause i'm guessing here:

    Relationships:
    1. A issue can only have one Technician:
    One to One
    Note: of course this may not be true and if it's not then you would need to associate the dates/times with the technician. I would keep this one-to-one for now since your staff is not large.

    2. An issue can only have one customer:
    One to One

    3. An issue can only have one type:
    One to One

    4. An issue can only have one priority:
    One to One

    5. An issue can only have one workstation:
    One to One
    Note: consider linking workstation to the customer instead of the ticket table...This way when you choose the customer that is calling their workstation information will pop up automatically. Mainly this would be a one to many scenario (a customer can belong to many workstations - some have a PC and a laptop) but there is potential for it to be many to many (a customer can have 2 computers and a computer can be used by more than one customer) I would keep it simple and do a one-to-many.

    Your other workstation Tables look ok to me and they all seem to be one to one relationships.


    Tables:
    Ticket (ticketID, TechnicianID, CustomerID, dateOpened, dateAssigned, DateStarted, dateClosed, DateResponded, DateCompleted, Issue, Resolution, TypeID, PriorityID

    Technician (TechnicianID, name, Phone, Email)

    Customer (CustomerID, Name, phone, location, email, WorkstationID)

    Type (TypeID, Type)

    Priority (PriorityID, Priority)

    Workstation - same as you have it minus the user

    This should get you started...let me know if any of this is confusing. Don't forget to record not just date but time also in the date fields.
    "If you are good you will be assigned all the work. If you are really good you will get out of it."

  9. #9
    Join Date
    Mar 2003
    Posts
    225
    thank you very much, will have a play around and let you know if i have any trouble

    once again thatnks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •