Link tbl keys...which way to go? (1 Viewer)

[Sergeant]

Hi Forum,
I'm building a db to track computer and user problems for work, where I double as a Client Support Administrator (IT Guy).
I have these tables: (simplified list)

tblComputers
tblPeople
tblCompAssignment
tblProblems

I'm at a loss as to how to link the 'Problems' table. This is where I'll record trouble tickets reported by users.
Incoming trouble tickets could relate to the Computer or to the User's Network Login.
I thought I might have a field in the problems table for UserName and also one for ComputerID, but this seems problematic. Anyone got any ideas?

 

[littlegod]

hi sergeant,
i take it that tblPeople is not the same as tlbComputer, meaning that one user can access your db from different computers.
maybe you could differentiate between types of trouble tickets (Problems), one type relates to the user, the other to the computer.
have a tblProblemsPeople and another tblProblemsComputer.
not sure if this would work, but i do understand what you mean, i think it would be impossible to link both peopleID and computerId to one tblProblem.

dan

 

[ScottGem]

If a person can report problems relating to different computers (i.e. a person is not assigned to a specific computer, then you have to use foreign keys to both the person and computer tables.

 

[Sergeant]

Thank you both for your inputs on this matter. I think I need to elaborate a bit more...
This application is aimed at the IT team tracking computer and user problems. Initially, the IT staff will enter reported problems into the db...later, I may add self-reporting ability.
Each user will have an assigned computer (the one on their desk). They may, however, log into any computer in our domain.
The problem I'm having is that any trouble ticket could be user account-related or computer related (or even network related). If we enter a trouble ticket for a computer and later find it to be a user account problem, we no longer want it related to a computer...we want it related to a user account.

Would it be prudent to have fields in the tblProblems that relate to users and that relate to computers?
Additionally, on the computer-related side...we want the problem history to stay with the computer, even after it is issued to another user.

Is it more clear now?

I feel I've gotten pretty good at setting up logical table structures, but this one is challenging me a bit. Any help is appreciated.

 

[ScottGem]

Doesn't change what I said. Since its possible that the computer is not the one normally assigned to the user reporting the problem, then you need capture the info. Later on, once the problem is accurately diagnosed you can remove the computer FK if you want.

 

[Sergeant]

Good point. I know it will defy normalization rules, but I'm gonna put a field in the problems table for ComputerID and a field for UserID. We can fill those/remove as necessary in the process.

An additional thought is to put another field in the problems table to define what type of problem it is, i.e...User/Computer/Network???

 

[littlegod]

Hi Sergeant,

Do you want the fields in tblProblems to be memos?
Are the problems actually described in the field?

Dan

 

[Sergeant]

Hi Sergeant,

Do you want the fields in tblProblems to be memos?
Are the problems actually described in the field?

Dan

Yes, that's how I'm currently doing it. What do you think?

 

[ScottGem]

Good point. I know it will defy normalization rules, but I'm gonna put a field in the problems table for ComputerID and a field for UserID. We can fill those/remove as necessary in the process.

An additional thought is to put another field in the problems table to define what type of problem it is, i.e...User/Computer/Network???

The type of problem is a good idea, from there you can limit your link to one field. with the type field defining what you are linking to.

 

[Pat Hartman]

Sorry Scott but you need to keep separate fields for User and Computer otherwise you will not be able to enforce RI. The UserID should always have a value and the ComputerID would be optional.

Technically, the ComputerID would be stored in a child table since it is optional but if that is the only piece of extra information, then, storing it in the parent record would be an OK violation.

 

[Sergeant]

Sorry Scott but you need to keep separate fields for User and Computer otherwise you will not be able to enforce RI. The UserID should always have a value and the ComputerID would be optional.

Technically, the ComputerID would be stored in a child table since it is optional but if that is the only piece of extra information, then, storing it in the parent record would be an OK violation.

Thanks, Pat, for weighing in here. I always value the advice you offer on this forum.
On the subject of RI, if I have a problem related to both a user and a computer, and then I delete the user...I wouldn't want to lose the computer problem history. Should I just not select the 'cascade delete related fields'?
Do you foresee other difficulties with the overall approach?

I have toyed with the idea of having a 'RelatesTo' table, but I think I would have the same problems.

 

[Pat Hartman]

If you want to keep the problem history, you will not be able to delete the user. I would not specify cascade delete on this relationship. With cascade delete NOT specified, you will receive an error if you try to delete a user with related problem records. With cascade delete specified, the problem records for a user will be deleted if the user is deleted.

You need to add some termination date field to the users so you can prevent assigning equipment to a terminated user but still retain the user so that the historical record is still valid.

You have the same problem with equipment. Once a problem has been reported, you can no longer delete the equipment.

 

[Sergeant]

I have a terminate date field in the tblCompAssignment (to show that the person no longer has that computer.) You make a good point, though...I should put a terminate employment date in the people table.

I think the answer on the RI is to show terminations in the db instead of deleting records. After all, there are only about 50 people and 70 computers, with a turnover of about 5-6 people per year and about 10 computers per year. The people would probably have microchips installed in their heads before db size would become a problem.

Thanks for the help.