I am pretty new to relational databases, so please be gentle... I'm looking for some pretty basic design ideas for the best way to build a database that is efficient and will compile data run over time, allowing me to manipulate it through reporting/queries (which I suppose is the whole purpose of relational databases anyway). I have worked on a few previously designed databases and have been able to pick through them and get the idea of how they are set up and build off of them, but for a ground-up build, I am a little in the weeds, and don't want to go too complex, too quickly.
Here's the situation:
I have a call center that just got a new NEC phone system, and the reports natively available from the system are (unsurprisingly) not so useful on their own.
I can run several reports, and download the data, but with some idiotic idiosyncrasies. I thought I could start off with dealing with just one of these reports, get the guts of the database in place and then add more reports/data after I have a good foundation from which to expand.
For example: A report called "Agent Call Summary" gives the following fields:
- 'Agent' (in an infuriating "Name - xxx" format, where xxx is the extension number)
- 'Time Logged in' (in hours:mins:secs)
- 'Number of Calls'
- 'Avg Calls per hour'
- 'Number of unanswered calls'
What is NOT INCLUDED ANYWHERE in the .csv is the freaking DATE the data is from!
Now, I have written some queries to break up the agent field into separate name/extension fields, and created a couple of new fields manipulating some numbers, but I don't know a few things:
How do I set up my database so that it is centered around either an agent name or extension number (both of which should remain constant over time, i.e. person 1 will always be at extension 1, etc.)
How do I best connect the queries that I have written to the data originally downloaded? (this might be worded weird, but I have functional queries that create new fields by taking the original data from an external file), but I don't know what to do with it once I have it, so that everything is kind of back in one place.
How can I append a date field to the data each time I download it (assuming I run it once per day at the end of the day), so that I can throw it to an Archive table, and then pull data by dates or date ranges?
OK. I hope that's not too much rambling, and that it makes sense what I am trying to do. At the end of the day, I want to be able to choose a date, or range of dates for which I can print a report of the data that I pulled from this report, adding some fields that I created to show, say a ratio of answered to unanswered calls, etc., and then can further pare it down by agent if necessary. (basically, what did agent 'a' do on days 'x,y,z')
Thanks for your help - feel free to point me to resources that may be better suited to step me through this.