Data Normalization

[Karen]

I am having difficulty with normalizing data. I am creating a client call
center database. Currently, when a call comes in from a Client Contact, the
users enter information regarding the Member’s issue on a data entry form.
The users can resolve certain issues themselves, but may need the additional
help of the Services Department. The users click on a button on the form and
open a new Services Form with the some of the data populated from the data
entry form. Additional fields on the Services Form are required to be
completed before handing over the issue to the Services Department.

A Client Contact may call in many times for issues regarding Members.
There may be several Client Contacts for a client. Some Client Contacts are
employees of the Client and some are employees of my company using the
database. Employees of my company also have many clients in their territory.
Only one Member can be documented on the issue form.

 

[Anand.V.V.N]

Hi Karen,

What you can do is have a field which would store how many times the
customer called to solve a particualr issue i.e. a client calls 1st time for
an issue, he calls the 2nd time for the same issue increment the count again
by 1. And for the additional contacts you can have a new table that stores
only the contact information and link this table with client table using the
client id from the client table.

Hope you found this helpful.

Anand

 

[Jeff Boyce]

Karen

If your underlying database contains one record per call, you wouldn't need
to add a field to track how many times: a given member called; a member
called about the same issue; or the number of issues referred to your
Service Dept. These can all be obtained by a query or by a DCount()
function.

I'm not at all clear, though, what data structure your system currently
has -- your post seemed to focus more on the forms being used.

One approach to normalization (if you and I are using the term the same way)
is to turn off your computer and use paper and pencil. Draw yourself a box
for each of the "entities" about which you will want to keep data
("attributes"). Then draw the lines ("relationships") between the boxes
(entities) that you believe are related. Jot down the "cardinality" (one
row in Entity1 can have many rows in Entity2 -- this would be typical for an
Order -- Order Detail relationship).

Now, for each of the boxes, add in the attributes. What facts about this
entity will you need to store? If your entity is connected to other
entities with lines, you'll also need to have a primary and/or a foreign key
field to make the connection.

Make sure your attributes are "atomic" -- that is, one fact, one field.

Make sure you don't record the same attribute in more than one Entity
(exception: primary/foreign key).

Without more of an idea of what your current structure looks like, this is
only a start...

Regards

Jeff Boyce
<Access MVP>

 

[Karen]

Thanks for the advice. These are the tables I have: ISSUE (houses all issue
related fields, whether or not it is handled by the call center or the
services dept. The table is tied to the Member table by the Member ID),
MEMBER(houses member info and is tied to client by client id), CLIENT
(houses client related info), SUBCODE (houses subcodes for client and is tied
to client table by client id), ADMIN (names of users of the database), AEAM
(Account Executives or Account Managers (our reps for the clients).

When filling out the form, the user Selects her name from the drop down,
selects the AE name for the client from a drop down, selects the client id
from a drop down(which auto fills in the client name), selects a particular
subcode from a drop down for the client. Types in the Contact Person
name(person who called in - can be AEAM or HR rep of client), enters contact
phone, fax (if applicable), and email. The user then selects other field
drop downs concerning the issue, enters the Member name and member id,
selects a call type, and enters the issue description.

When looking at the issues table, I noticed that a contact person name may
be in the table multiple times, each time they called in with an issue.

When looking at the Member table, there are many member names in there more
than once, depending on how many times they have had an issue.

Also, there could be two issues for one call. I am not sure how to handle
this type of situation. I can't have a call type 1 and call type 2 in the
table.

This database has grown since its early, simple version. I want to
restructure to make it more efficient.Thanks for all your help.

 

[tina]

When looking at the Member table, there are many member names in there
more

than once, depending on how many times they have had an issue.

there shouldn't be. you said the Members table "houses member info". each
member record should be unique, with a primary key to identify it. if the
Members table is linked to the Issues table, then you should see various
members listed in the *Issues* table multiple times: one member may be
associated with many issues, but each issue is associated with only one
member.

Also, there could be two issues for one call. I am not sure how to handle
this type of situation. I can't have a call type 1 and call type 2 in the
table.

sounds like you need a Calls table to indicate information about the call
itself: and a "subtable" for CallDetails (Issues), so that you can list
multiple issues for one call when necessary. example:

Calls
CallID (primary key)
Caller (Member?)
CallDate
(other fields that describe the entire call.)

Issues
IssueID (primary key)
CallID (foreign key from table Calls)
IssueDescription
HandledBy
(other fields that describe a specific issue in a specific call.)

the relationship between the two tables is
Calls 1:n Issues

This database has grown since its early, simple version.

yes, databases have a tendency to do that. <g> that's why proper
table/relationship design is so important even in "simple" databases. that
doesn't mean you would *never* have to restructure a database when the
process grows, but it's always easier to expand when you're working from a
solid foundation.

hth

 

[Karen]

I am not sure how to get the user to either select the member or enter a new
one. I don't see them wanting to use a drop down and look for the member
name for each client that is entered. Would I need a member table if they are
not interested querying on the member information?

 

[Jeff Boyce]

Karen

When I read back over your posts, I have a concern that you are focused on
making forms before you have your data well-structured.

You don't HAVE to normalize your data to use a relational database (like
Access), but you WON'T get to use the product's strengths if you don't.
After all, you could drive nails with a chainsaw, but ...

The feedback you've been getting has pointed to stepping back from your
current design and forms. Is that somehow not an option? Remember, you
posted here asking for idea... <g>

Regards

Jeff Boyce
<Access MVP>

 

[tina]

Karen, you're really in the best position to analyze your company's process,
and determine what entities need to be tracked, what characteristics of each
entity to store, and how the entities are related. this is called relational
data modeling, often referred to as normalization. those of us answering
your posts are just making educated guesses based on your descriptions. i'm
in the same boat as Jeff - i'm not clear on either your process or the
relationships between your entities (real world, not necessarily in your
current db).

suggest you invest some time in learning the basics of data modeling, so
your database's foundation will be solid, run well, and be much easier to
modify and expand upon. one good text to help you is Database Design for
Mere Mortals by Michael Hernandez. also, see
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Database Design 101 and Starting Out links.

hth