Help and Advice- Helpdesk Relationships

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 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....

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

AS far as the following tables:

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

Yes I would keep them seperate. People would disagree with me, but I like to keep my designs flexible enough that changes to look up items, such as these, are data changes and not programming changes. You could actually add more, if you like.

As far as your design structure, I would add a relationship from workstation to helpdesk, based on WorkstationID. I would also remove loction from Helpdesk, unless it refers to something different then workstion location.

I would probable add some tables to keep track of the software loaded on the workstations. (this will help you with some troubleshooting, and help keep track of your licences)

As far as want you need, just keep in mind, what information you need to track, what kind of reports you need to come out of the system, and what your system rules are (what kind of situations do we face, how do we handle them (process situations)) and design and progam to meet your needs.

S-

thanks for your help, i was wondering if you could take a look at my database and see if you think if i need anything else in my tables or indeed any other table.....

i need this lot to all be to 3NF.......dont get this at all.

i also need to sort out the relationships, i dont fully understand the one to one to many etc, i get really confused here.

picture of table relationships:



and my database is attatched:

cheers

Andy

You DB is lock into a WOrkgroup so I can't open it, but after looking at the ERD, the only think I would think about would be the following.

There is the potentail to create other lookup tables if you desire.

If you are only going to allow 1 workstation to be assigned to a help ticket then i would consider combining the helpdesk and tickets tables. But if you can assign more then one workstation to a ticket then it looks fine.

You will always find ways to improve things as you go along, and practice makes perfect.

FYI: 3NF isn't always the best design for every system. Take you DB design to 3NF and then back off from there where it makes sense to break the rules (for example storing a calculated value).

S-