Search code examples
database-designnormalizationdatabase-normalization

Normalization for telephone support worker

I am creating a sql database which logs calls from companies (or separate departments within a company) with service contracts(1).

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.

This is how far i have got with normalizing the data. I'm not sure if i have chose the correct initial key. Also i'm not sure how to finish the 3rd normal form. Here is a picture of it.

Normalization

If any of you guys could help me i would be fantastic

*UPDATE OK i have had another go at normalizing it and here it is: (star = Foreign Key. double star = Primary Key)

**Call id**
Date and time of call
Method of call
*First line Support Analyst ID
*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 of action
Current status
Current severity level

**Specialist Analysts  ID**
Name of specialist analyst
Expertise

**First line Support Analyst ID**
Name of first line support analyst

**Problem Analyst ID**
Name of problem analyst

**Service contract number**
Company
Department
Software product
Software version
Operating system

NOTES

(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)

Solution

  • Without knowing your requirements, it's hard to be precise, but here are some notes:

    I would create an "analyst" table, with analyst_type, rather than seperate tables for each type of analyst.

    I would have a none-to-many relationship between analysts and expertise areas.

    I would create the explicit concept of "call_status", rather than depend on which analyst currently has the ball.

    I would consider having call_status, action and current_analyst in a separate table with date_time flags, so you can track history - when was it assigned to the 2nd line analyst? How long did they have the call? What did they do?