Defining a webservice for usage analytics (dekstop application)

Question

Current situation
I have a desktop application (C++ Win32), and I wish to track users' usage analytics anonymously (actions, clicks, usage time, etc.)
The tracking is done via designated web services for specific actions (install, uninstall, click) and everything is written by my team and stored on our DB.

The need
Now we're adding more usage types and events with a variety of data, so we need define the services.
Instead of having tons of different web services for each action, I want to have a single generic service for all usage types, that is capable of receiving different data types.
For example:

• "button_A_click" event, has data with 1 field: {window_name (string)}
• "show_notification" event, has data with 3 fields: {source_id (int), user_action (int), index (int)}

Question
I'm looking for an elegant & convenient way to store this sort of diverse data, so later I could query it easily.
The alternatives I can think of:

• Storing the different data for each usage type as one field of JSON/XML object, but it would be extremely hard to pull data and write queries for those fields

• Having extra N data fields for each record, but it seems very wasteful.

Any ideas for this sort of model? Maybe something like google analytics? please Advise...

Technical: The DB is MySQL running under phpMyAdmin.

Disclaimer: There is a similar post, which brought to my attention services like DeskMetrics and Tracker bird, or try to embed google analytics to C++ native application, but I'd rather the service to by my own, and better understand how to design this sort of model.

Thanks!

Answer 1

This seems like a database normalization problem.

I am also going to assume that you also have a table named events where all events will be stored.

Additionally, I am going to assume you have to the following data attributes (for simplicity's sake): window_name, source_id, user_action, index

To achieve normalization, we will need the following tables:

events
data_attributes
attribute_types

This is how each of the tables should be structured:

mysql> describe events;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| event_type | varchar(255)     | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

mysql> describe data_attributes;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| id              | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| event_id        | int(11)          | YES  |     | NULL    |                |
| attribute_type  | int(11)          | YES  |     | NULL    |                |
| attribute_name  | varchar(255)     | YES  |     | NULL    |                |
| attribute_value | int(11)          | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

mysql> describe attribute_types;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| type  | varchar(255)     | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

The idea is that you will have to populate attribute_types with all possible types you can have. Then, for each new event, you will add an entry in the events table and corresponding entries in the data_attributes table to map that event to one or more attribute types with the appropriate values.

Example:

"button_A_click" event, has data with 1 field: {window_name "Dummy Window Name"}
"show_notification" event, has data with 3 fields: {source_id: 99, user_action: 44, index: 78}

would be represented as:

mysql> select * from attribute_types;
+----+-------------+
| id | type        |
+----+-------------+
|  1 | window_name |
|  2 | source_id   |
|  3 | user_action |
|  4 | index       |
+----+-------------+

mysql> select * from events;
+----+-------------------+
| id | event_type        |
+----+-------------------+
|  1 | button_A_click    |
|  2 | show_notification |
+----+-------------------+

mysql> select * from data_attributes;
+----+----------+----------------+-------------------+-----------------+
| id | event_id | attribute_type | attribute_name    | attribute_value |
+----+----------+----------------+-------------------+-----------------+
|  1 |        1 |              1 | Dummy Window Name |            NULL |
|  2 |        2 |              2 | NULL              |              99 |
|  3 |        2 |              3 | NULL              |              44 |
|  4 |        2 |              4 | NULL              |              78 |
+----+----------+----------------+-------------------+-----------------+

To write a query for this data, you can use the COALESCE function in MySQL to get the value for you without having to check which of the columns is NULL.

Here's a quick example I hacked up:

SELECT  events.event_type as `event_type`, 
        attribute_types.type as `attribute_type`, 
        COALESCE(data_attributes.attribute_name, data_attributes.attribute_value) as `value`
FROM    data_attributes,
        events,
        attribute_types
WHERE   data_attributes.event_id = events.id
AND     data_attributes.attribute_type = attribute_types.id

Which yields the following output:

+-------------------+----------------+-------------------+
| event_type        | attribute_type | value             |
+-------------------+----------------+-------------------+
| button_A_click    | window_name    | Dummy Window Name |
| show_notification | source_id      | 99                |
| show_notification | user_action    | 44                |
| show_notification | index          | 78                |
+-------------------+----------------+-------------------+