Contact Center DB

[tduffield89]

Hi,

I have been tasked with creating a contact center database. The database is required to include a form from which handlers can log calls and includes logging guidance, table(s) storing this information, reports displaying various KPI's from this data.

I am come from an excel background and I am struggling with the concept of relational data. The current system the organisation runs is on excel. Each handler has an excel logging form that has a front end with dependent combo boxes and a button that transfers the data to the back end. This data is collected on a monthly basis and plugged into the dashboard.

The organisation now wants live reporting, call logging with handler guidance and reporting all on one platform.


The data is composed of the following categories:
1 - Call Month opened date
2 - Caller location: Business geographical network, country, city
3 - Call source: who is calling (list of categories), who it is about (list of categories)
4 - Call subject: category then subcategory
5 - Action Taken: Dependently linked to the selections made in 5 above



I have created a logging from that seems to work fine. The data from this form is added to a single table, is this the correct design? From this mastersheet I then attempt to pull reports through queries.

With the queries should I be looking for duplicates in order to count the instances of, for example, the monthly call volumes related to a city?

I am bit lost to be honest and I don't know whether this is because of fundamental design flaws.

Any guidance would be much appreciated, sorry for the essay.

[ssanfu]

First start by working through the tutorials at Rogers Access Library at
(1) http://www.rogersaccesslibrary.com/f..._topic238.html
and
(2) http://www.rogersaccesslibrary.com/f..._topic237.html

Use pencil and paper to start designing your tables. Post back here if you have questions or want a review of your structure.
Then you can start developing the queries, forms and reports.

You have an advantage because you already have an idea of what data you need to gather.

Suggestions:
http://access.mvps.org/access/tencommandments.htm
http://access.mvps.org/access/lookupfields.htm

[tduffield89]

Thanks for the prompt reply.

I think I understand the basics of the design process but I am struggling to see how that fits with data I already have.

I have several tables supporting the selection options of the combo boxes on the recording form. Such as networktable, countrytable, callcategories etc. These are all related and the combo boxes work fine. My main question now is where should the completed enquiry data go? Should each enquiry be chopped up into the segments described above or should it just go into a enquiry data table whole? I tried to mirror the call tracker template from microsoft but now I am questioning whether this is the correct model for the existing data I have. If it should go into one table, what is the best method to report for example monthly call volumes for each city, or monthly volumes of specific enquiry subjects for a city?

Below is a screenshot of my form.

Thanks again.

[ssanfu]

A single table could work, but I don't know the relationships among the tables.
What tables/fields do you have so far?
Maybe post a pic of the relationship window.


-------------
BTW, it looks like there might be an "Enquiry Number" in the upper right hand corner of the form and that it is an Autonumber.
If so, I would advise against displaying the autonumber on the form.

See "Autonumbers--What they are NOT and What They Are"
http://www.utteraccess.com/wiki/index.php/Autonumbers

If you need a number for each "Enquiry", you will have to "roll your own" (a custom autonumber).
There is a example of doing so at http://www.baldyweb.com/CustomAutonumber.htm

[orange]

Further to Steve's comments, I like to see a list of Business Rules. These are critical to get all tables and attributes and to establish relationships in your database.
This is how you ensure your data base matches your business requirement.
A tutorial to lead you through the steps is at http://www.rogersaccesslibrary.com/T...lationship.zip

[DriveStop]

tduffield89

you have the diagram of database?
I can try to do for you database. I created a tool like MS Access, and now need to test it.

[Dal Jeanis]

The problem you are experiencing is conceptual. You are asking us the big-picture question "how should I store this information".

The answer is, you break it down and store it in groups of information related to the entities that your are tracking, and in such a way that you will later be able to draw out the business knowledge that is inherent in the data.

That means, you have to be able to tell us what you think you will use the data for, for us to give you valid guidance on how to design the relationships.

Given your question, I believe the answer is this -

If no particular call requires a completely different category of information be stored, then put all the information about each call in a single call table.

On the other hand, if there are types of calls you will track that are qualitatively different, such as calls from VIPs, or calls which were escalated to supervisor level, or whatever, then you keep the different part of the data in a different table, and use the primary key of the regular call record as a foreign key on the special call record, to record the fact that they are related.

All of the reporting by call type, by region, or by whatever else, such as date, can be handled with a single call table, as long as the desired information is in that table, or can be related to that table by a foreign key.

I'm quoting myself there, but I wanted to set it off visually from the more general advice I'm giving here.

All your questions will be answered quickly and clearly, and you will save yourself hours of headaches, if you would just follow ssanfu's advice in post #2, despite the fact that those tutorials seem very basic and simple, and then post your preliminary design here for comments and advice.

Or, alternately, you can do it the hard way, and dive in without nailing the basic design first. It just depends on how interesting you want to make your own job.

[tduffield89]

Excellent advice, thank you.

I will sit down and study the basics more closely then come back with a design. However, my initial though is that the data should sit in a single table.I think the biggest problem I am having is that I have existing flat data and deciding how to chop this up doesn't seem to match with any of the tutorials I have read. In essence what I want to record are enquiries about set locations. My locations, call subjects, caller info and action taken options are all sat in related tables that drive the input form. But when it comes to deciding how to store the data I go back and forth between the 2 options you offer above.
.

[tduffield89]

@ Orange: The relationships I have are for tables that support the combo boxes in the input form. I.e. Network - country - city etc. The data that comes out of the form goes into one table that is not related to any other.

[Dal Jeanis]

So far, in your description, I haven't seen any great reason to break the call records themselves into more than one record. The main question is what fields are needed to be stored on that record, and which can be picked up by inference from the fields that you are storing.

EXAMPLE OF POTENTIAL OPPORTUNITY FOR NORMALIZATION

Look at the flat table you already have. Are there fields on the table that always go together? Then they are candidates for being on a separate table. You should decide to break them out only when it makes sense in context and when it helps maintain the integrity and simplicity of the database.

For example, I see several records where Country = Italy and Post = Rome. If a particular enquiry is at the Post in Rome, is Country always going to be Italy?

Further, is the Country always going to be a unique specific country for every given Post?

Further, can you be absolutely certain that there are no Posts that are on the border between countries, or that will handle enquiries for more than one country?

If so, then only the Post needs to be stored in the flat record, and the relationship of Post to Country can be stored in a different table. You can use that second table in dropdowns on the form to help the user locate the appropriate Post, but you don't need to store the country on the flat table, because the country is always uniquely determined by the post.

On the other hand, you don't have to fully normalize the data like that, if you have a design consideration that gives you a reason not to.

You can continue to store both the Country and the Post, just so long as you understand that doing so means that if a user changes the Post on a record, and forgets to change the Country, or vice versa, then your data won't be entirely accurate. If you denormalize the data for your convenience, then you have to control the data integrity through your design of the forms and the workflow. (Or through other constraints on the tables, which are beyond the scope of this thread.)

EXAMPLE OF REASONABLE DENORMALIZATION

Proper normalization is always informed by the way the information will be used, in real-life practice.

Nowadays, contact information such as telephone numbers is usually normalized into a separate table, because personal phone numbers have multiplied to the point that most people have at least three of different types -- home, work, cell, fax, text/pager, etc.

However, it can also be reasonable to retain the 1970's standard of normalization and have two or three fixed slots on a fixed record layout to hold particular types of phone numbers in each slot. It all depends on how the data will be used, and by whom.

The denormalized method means that to identify the perosn to whom a particular phone number belongs, you have to search three different fields, instead of searching a single field in a related table. If you don't ever have to search that way, then perhaps the fixed method makes more sense for your application.

AVOID LOOKUP FIELDS

Whichever way you proceed, the important thing about table design and storing the information is not to use lookup fields, but instead to store the foreign key, not the name, of the Country and/or Post (for example). Building "lookup fields" into the design of the table has been shown to lead to various kinds of trouble. Primarily, what you see when you look at the table is not what you get - you may see CountryID = "Italy", but what you have actually stored there is CountryID = "19". Every time you design a new query, you have to ascertain what kind of field you are really trying to join to, or you get nonsense or no results. There are other disadvantages, but that one should be enough to dissuade you.

If not, here is the full gospel that Lookup tables are the invention of the Evil One: http://access.mvps.org/access/lookupfields.htm

Now that I've (hopefully) convinced you, it's time to also remind you that in some situations, such as web apps or sharepoint, then lookup fields may also be mandatory. I'll also give a pointer to the minority point of view that lookup fields are just "misunderstood" rather than being completely evil. http://improvingsoftware.com/2009/10...cess-are-evil/

[tduffield89]

Dal,

Thank you for your detailed advice.

1. Yes, the country and network will always remain the same for a specific Post. E.g. Rome - Italy - Southern Europe.
2. There are multiple Posts per country
3. There are no Posts that operate cross-border


I currently have tables that support the combo boxes such as:




Can I use the NetworkID and CountryID as foreign keys in the Calls table? Thus replacing the repeated text? If so how would I do this in my input form? I still want the users to use the dependent combo boxes as this makes for a far easier logging process. So my question is how do I get my form to add the foreign keys of Network ID and Country ID for a post selection. Currently when I select a network form the combo box on my form this copies across to my calls table with the complete record. Is there a way of changing the properties of the combo box to input the foreign key form my support table instead of the text I select form the combo box?

[tduffield89]

My relationships: The second row of relationships simply operate to populate the combo boxes a make selections dependent.

[orange]

I'm not sure how much interaction your organization has with others, or how much, if any, analysis and tie-ins with various statistics or economic measures. It may be that your countries and regions have been "locally" defined. But I would like to mention that many organizations, especially government, look to using standard tables. Those are tables with some universal recognition, identified support and maintenance policies.

For example, Countries are identified by the ISO 3166 standard
Here is a link http://userpage.chemie.fu-berlin.de/.../ISO_3166.html

Countires and Regions are maintained by UN Statistics division
Here is a link http://unstats.un.org/unsd/methods/m49/m49regin.htm

I only mention this so you don't unknowing create your own tables and take on any associated maintenance. It may be that you'll never have to deal with these concepts, but at least you will know they exist.

Good luck with your project.

[Dal Jeanis]

Basically, your combo box can have multiple columns, and when the user selects a value, each relevant column can cause any necessary fields to get set.

Since each post uniquely determines several other fields, you could have the combo BoundColumn be the PostID, and have that field bound to the underlying record, and not save any of the other (redundant) values in the underlying record. Those other values can be determined in the query.

Looking at your relationships diagram, the Calls table should have PostID and be linked to the Post table. The Country and network then become redundant data, that are picked up from their relationships to the Post.

If each country is related to only one Network, then network should be removed completely from the Post table and only appear on the country table.

tblCalls
  EnquiryID     Autokey
  ...
  PostID        FK to tblPosts

tblPosts
  PostID        Autokey
  PostName      Text
  postCtryID    FK to tblCountries

tblCountries
  CtryID        Autokey
  CtryName      Text
  CtryNetwID    FK to tblNetworks

tblNetworks
  NetwID        Autokey
  NetwName      Text

Depending on how your screens are used, you probably want the user to be able to set up a default for the value of Post in the combobox. You could also allow the user to set an unbound combobox to a particular country, and the AfterUpdate event of that combobox could select the values for that Country into in the Post dropdownbox.

[tduffield89]

The reason I have my combo boxes set out as they are is because the dependencies limit incorrect entries by reducing possible choices. A huge list of Posts will, in my past experience, result in incorrect data entry. However, after reading various threads on the dangers of lookup fields I am a little concerned. What is the best workaround?