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
First, here is the entire ER diagram and a quick overview of the system.
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.
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) STAFF
|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
|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.
|list_id||INTEGER||Foreign key. The subscriber is subscribed to this list.|
|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
|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
|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
|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.
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
LINKS & REFERENCES
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!