0

For clarification, I don't want to store metrics on the database itself - rather, I want to build a database to store metrics from the various controls we measure at my organization for easy reporting. A little background: as manager, I pull metrics from various applications - our two ticketing systems (yeah, I know), our phone system, alerts from our event management software (i.e., Nagios), etc. I report on these on a weekly basis and keep an Excel spreadsheet with the historical data. The spreadsheet is really, really big and inflexible.

I'm not new at writing web apps, but I'm still new to the database design arena. I want to code an app with some awesome rickshaw javascript graphs for some great historical data (and to wow the senior management team with crazy colors and interactivity).

Where do I start with the database? I could create one table for all metrics, but how to index those into the various types (for instance, phone metrics has abandon rate, total inbound calls, total outbound calls, total time on call, average talk time, average hold time, max hold time, etc.). That's one messy, unorganized table.

I could create one table for each type (phone, ticket, event, etc.) but that seems hard to add metrics to the pile later.

I'm hoping someone here has some experience and can give me some pointers on what direction I should head.

PS: It will need to be SQLite or MySQL, just due to the resources I have available at this time.

Nathan Loding
  • 3,185
  • 2
  • 37
  • 43

1 Answers1

1

MySQL design for such a system can be made considering following:

  1. A Table for each type of metrics group for example an entity of ticket system can be a single ticket
  2. If a ticket is connected to single user you may include user name in the previous ticket table otherwise to keep it flexible i would say create a table for each connected element for example ticket is assinged to staff and has multiple telephone calls associated to it so you would need calls table and staff table.
  3. In order to map multiple items create mapping tables for example stafftickets and ticketcalls to associate staff with multiple tickets and tickets with multiple calls

Once you have defined these entities then you can sit on mySQL phpmyadmin and create tables that will work.

For charting side of things use D3.js and just spit out json and use javascript or json2 to bind it to your graphs etc.

Farrukh Subhani
  • 2,018
  • 1
  • 17
  • 25
  • So when I want to add a new metric, I'm creating a new table with all new relationship tables? That seems like too much work, but perhaps it is the best? – Nathan Loding Jan 31 '13 at 16:59
  • if you are adding new metrices every week or so then it is a lot of work but if you can imagine a set of metrics you can start with to show off skills and later on add to it. Use Scaffolding from Yii or PHPScaffold to generate repeated code. For example using a grid to show all columns of a table and plotting it in a line chart by one column and datetime can be created with two variables in hand. Then generate as many charts as you want. you would have more control and flexibility and with time it will become a complete solution. – Farrukh Subhani Jan 31 '13 at 17:05