Is this on the right lines (1 Viewer)

[acwest]

Hi,

I am just trying to normalize these data here:

An initial call is logged by a first line support analyst who assesses the severity of the problem and places the call in a pool. The call is picked up by a problem analyst who in turn allocates it to a specialist analyst based on their expertise, who will endeavour to provide a solution. This process is overseen by a support manager.

Problems are allocated in order of severity followed by date and time of submission (i.e. first in, first out). During its lifetime, a call is classified as:-

i. open and in pool awaiting allocation by problem analyst
ii. open and being worked on by specialist analyst
iii. open and awaiting more information from the customer
iv. closed

A call may loop between ii. and iii.

The following data are determined by first line support personnel based on the initial call:-

• Call id (to be automatically generated)
• Date and time of call (to be automatically assigned)
• Method of call (telephone, email or web)
• Id and name of first line support analyst
• Service contract number[1]
• Company
• Department
• Software product
• Software version
• Operating system
• Name of caller
• Phone number - landline or mobile or both
• Email address
• Brief textual description of problem (500 characters maximum)
• Initial severity level
• Action id (to be automatically generated)
• Call id
• Date and time of action (to be automatically assigned)
• Id and name of specialist analyst currently working on the call (a support manager may reassign a call to another specialist analyst based on load and expertise)
• Textual description (500 characters maximum) of any action taken by the specialist analyst whilst servicing the call.
• Current status
• Current severity level

Once a call has been allocated by a problem analyst, the Id and name of the problem analyst is added to the above data.

[1]A service contract relates either to a company as a whole or to a department, i.e. two departments in the same company will have separate service contracts, A service contract covers a distinct software/OS combination, so SQL Developer under Windows 7 would have a separate service contract to SQL Developer under Windows XP (or Windows Vista or MacOS Lion)

From the above i have normalized it like this but im not sure if its right:

Call id
Date and time of call
Method of call
*First line Support AnalystID
*Problem Analyst ID
*Specialist Analysts Id
*Service contract number
Name of caller
landline
mobile
Email address
Brief textual description of problem
*Initial severity level

Action id
*Call id
Date and time of action
Textual description
*Current status
*Current severity level

Specialist Analysts Id
Name of specialist analyst
Expertise

First line Support AnalystID
Name of first line support analyst

Problem Analyst ID
Name of problem analyst

Service contract number
Company
Department
Software product
Software version
Operating system

Current Status ID
Status Description

Severity Level
Severity level description

Is this normalized to third normal form?

 

[spikepl]

You could argue that

[FONT=Arial][SIZE=2]
Name of caller
[/SIZE][/FONT][FONT=Arial][SIZE=2]landline
[/SIZE][/FONT][FONT=Arial][SIZE=2]mobile 
[/SIZE][/FONT][FONT=Arial][SIZE=2]Email address
[/SIZE][/FONT]

will have a lot of repeats

[FONT=Arial][SIZE=2][U]Service contract number[/U][/SIZE][/FONT][FONT=Arial][SIZE=2]
[/SIZE][/FONT][FONT=Arial][SIZE=2]Company
[/SIZE][/FONT][FONT=Arial][SIZE=2]Department
[/SIZE][/FONT][FONT=Arial][SIZE=2]Software product
[/SIZE][/FONT][FONT=Arial][SIZE=2]Software version
[/SIZE][/FONT][FONT=Arial][SIZE=2]Operating system
[/SIZE][/FONT]

is not normalized at all. One company will have many service contract numbers, perhaps all pertaining to the same OS but different applications or viceversa and many companies will have service on the same applications or OSs.

Is there only one support manager? I'd have thought that the person doing the (re)assignment would get registered too. Also "linger time" does not seem calculable, unless a (re)assigment itself also is considered an action.

 

[MarkK]

I would expect all the analysts to be in the same table.

 

[Lightwave]

As per Spikes implied inference I would make a caller table up and have the caller and the specialist analyst in the same table.

And I would also concur with Lagbolt

Analysts and Callers are one and the same generic types ie people and you may have some analysts who are also callers. If you wanted you could have a flag on this new Person Contact Table which would indicate whether they are an analyst at present. I had a similar situation in a booking system for lectures. A course is taken by a lecturer but all the fields of the lecturer are exactly the same as the fields required for the students. By combining them in the same table you reduce the need for the same form referencing different tables. There is a tendency to make two forms instead of one form in cases where you have different tables as the data binding varies. If your forms are complicated combining the tables will tend to reduce form design work.

The flag system is used so that if you need a short drop down of just the analysts you run a query filtering out non analysts and reference this query in a combo.