Call Center Management System ERD (With Database Structure)

Welcome to a sharing of an ERD of a call center management system. So you have taken on a call center project, or got this as a school assignment? Well, here is a sharing of my simple version – Read on!

 

 

TABLE OF CONTENTS

 

 

CALL CENTER MANAGEMENT SYSTEM ERD

To keep things simple, I have grouped this system into 3 sections.

• Staff – Administrators, managers, telemarketers, etc…
• Subscribers – List of people to call.
• Campaigns – When to call, for what purpose, which list.

 

FOR REFERENCE ONLY

I can hear the “professional trolls” rearing to share their “expert opinions”. “This is so dumb”, “it’s inaccurate”, “you should have done this and that”. By all means, this is only a simple example.

• If the call center is calling on behalf of other companies, go ahead and create another “company” entity/table. Also, add it to the subscriber list and campaigns.
• If you want “added security” to limit which staff has access to which list, go ahead and add another “list access”.
• If there is only one subscriber list, go ahead and remove it.

Yes, you are creating your own system now. You decide your own rules and do whatever makes sense to you.

 

 

 

PART 1) STAFF

COLUMN	DATA TYPE	NOTES
staff_id	INTEGER	Primary key.
staff_type	VARCHAR	Up to you to decide – “A”dmin, “M”anager, “T”elemarketer, etc…
staff_name	VARCHAR	The staff’s full name.
staff_email	VARCHAR	The staff’s email. Set this to unique to prevent duplicates.
staff_password	VARCHAR	The login password.

Take note that there are 2 “users tables” in this project, this one is for internal staff members.

 

PART 2) SUBSCRIBERS

2A) SUBSCRIBER LIST

COLUMN	DATA TYPE	NOTES
list_id	INTEGER	Primary key.
list_name	VARCHAR	Subscriber list name.
list_description	VARCHAR	A description of the subscriber list.

So, just why is there a need for a “subscribers list”? Consider these:

• A call center can be calling on behalf of companies. Different lists can belong to different companies.
• Even if that is not the case, a company may also have multiple lists of subscribers with different interests.

 

 

2B) SUBSCRIBERS

COLUMN	DATA TYPE	NOTES
subscriber_id	INTEGER	Primary key.
list_id	INTEGER	Foreign key. The subscriber is subscribed to this list.
subscriber_name	VARCHAR	Subscriber’s name.
subscriber_email	VARCHAR	Subscriber’s email.
subscriber_tel	VARCHAR	Subscriber’s telephone number.
subscriber_status	VARCHAR	Up to you to decide – “A”ctive, “V”IP, “D”eactivated.

This should be entirely self-explanatory, but take note of the “subscriber status”. In certain regions, it is OK to “deactivate” the subscriber but keep the data. But in certain regions (such as EU), there are data protection laws – You will have to comply and delete the subscriber from the database upon specific request.

 

PART 3) CAMPAIGNS & CALLS

3A) CAMPAIGNS

COLUMN	DATA TYPE	NOTES
campaign_id	INTEGER	Primary key.
campaign_name	VARCHAR	The campaign name
campaign_notes	VARCHAR	Campaign notes, if any.
campaign_start	DATE TIME	Campaign start date.
campaign_end	DATE TIME	Campaign end date.

This should be self-explanatory as well, some examples for those who are lost:

• Christmas promotion.
• New product launch.
• Mid-year sale.

 

 

3B) SUBSCRIBER LIST TO CAMPAIGN

COLUMN	DATA TYPE	NOTES
campaign_id	INTEGER	Primary and foreign key.
list_id	INTEGER	Primary and foreign key.

Because a campaign may involve more than one subscriber list.

 

3C) CALL HISTORY

COLUMN	DATA TYPE	NOTES
call_id	INTEGER	Primary key.
campaign_id	INTEGER	Foreign key, which campaign is this call for.
subscriber_id	INTEGER	Foreign key, calling which subscriber.
staff_id	INTEGER	Foreign key, the telemarketer.
call_start	DATE TIME	Time of call.
call_end	DATE TIME	Time when call ended.
call_notes	VARCHAR	Notes, if any.

Finally, a log of all the calls.

 

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart