Defining a web service to use analytics (dekstop app)

Question

Current situation
I have a desktop application (C ++ Win32) and I want to track the anonymity of user usage (actions, clicks, usage time, etc.)
Tracking is carried out through certain web services for certain actions (installation, uninstallation, click), and everything is written by my team and stored in our database.

Necessity
Now we are adding more types and usage events with different data, so we need to define services.
Instead of having many different web services for each action, I want to have a common single common service for all types of use, which is able to accept different types of data.
For instance:

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

Question
I am looking for an elegant and convenient way to store such diverse data, so later I could easily request it.
Alternatives I can think of:

• Saving different data for each type of use as one of the fields of the JSON / XML object, but it would be extremely difficult to extract the data and write queries for these fields

• Having additional N data fields for each record, but this seems very wasteful.

Any ideas for this kind of model? Maybe something like google analytics? please advise...

Technical:. DB is MySQL running under phpMyAdmin.

Disclaimer: There is a similar post that brought to my attention services such as DeskMetrics and Tracker bird , or try to integrate google analytics into your own C ++ application, but I would prefer the service myself and better understand how to create such a model.

Thanks!

Answer 1

This seems like a database normalization problem.

I also assume that you also have a table called events where all events will be saved.

Also, I'm going to assume that you have the following data attributes (for simplicity): window_name, source_id, user_action, index

To achieve normalization, we need the following tables:

 events data_attributes attribute_types 

Here's 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 need to fill attribute_types all possible types that you have. Then, for each new event, you add an entry to the events table and the corresponding entries in the data_attributes table to map this event to one or more attribute types with the corresponding 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} 

will display 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 checking which of the NULL columns.

Here is a quick example that I hacked:

 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 gives the following result:

 +-------------------+----------------+-------------------+ | 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 | +-------------------+----------------+-------------------+ 

Answer 2

EDIT: Bugger! I read C # but see that you are using C ++. Sorry about that. I leave the answer as it is, since its principle can still be useful. Please consider the examples as pseudo code.

You can define the custom class / structure that you use with the array. Then serialize this data and send it to the WebService. For instance:

 [Serializable()] public class ActionDefinition { public string ID; public ActionType Action; // define an Enum with possible actions public List[] Fields; //Or a list of 'some class' if you need more complex fields } List AnalyticsCollection = new List(Of, Actiondefinition); // ... SendToWS(Serialize(AnalyticsCollection)); 

Now you can dynamically add as many events as you want, with the necessary flexibility.

on the server side, you can simply analyze the data:

 List[of, ActionDefinition] AnalyticsCollection = Deserialize(GetWS()); foreach (ActionDefinition ad in AnalyticsCollection) { switch (ad.Action) { //.. check for each action type } } 

I would suggest adding security mechanisms such as a checksum. I assume that de / serializer will be pretty common in C ++, so maybe a simple Base64 encoding can do the trick, and it can be wrapped as ascii text.

Answer 3

You can create a table for each event in which you declare which parameter means what. Then you have the main table in which you enter only the name of the event and param1, etc. The admin tool will be very simple, you will go through all the events and describe them using the table in which each event is declared. For instance. for your button_A_click event, you insert into the description table:

 Name Param1 button_A_Click WindowTitle 

So, you can group your events or select only one event.

Here is how I could solve it.