Member Avatar for gunnerone

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?

Edited by gunnerone because: n/a
  • 2 Contributors
  • 1 Reply
  • 141 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by BitBlt
Member Avatar for BitBlt

I would say no, because of the whole multiple Analyst thing.

I would combine all the Analyst types into a single Analyst table and put three non-identifying relationships to Call (with appropriate role-names for the foreign key attribute names). That way you can have the same analyst be First-Line, Problem, or Specialist without having to have three entries in the three different tables.

Otherwise, it looks good.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.