mysql - 定义用于使用分析的 Web 服务(桌面应用程序)

Question

现状
我有一个桌面应用程序 (C++ Win32)，我希望匿名跟踪用户的使用分析(操作、点击、使用时间等)
跟踪是通过针对特定操作(安装、卸载、单击)的指定 Web 服务完成的，所有内容均由我的团队编写并存储在我们的数据库中。

需求
现在我们正在添加更多使用类型和事件以及各种数据，因此我们需要定义服务。
我不想为每个操作提供大量不同的 Web 服务，而是希望为所有使用类型提供一个单一通用服务，该服务能够接收不同的数据类型。
例如:

• “button_A_click”事件，包含 1 个字段的数据:{window_name(字符串)}
• “show_notification”事件，包含 3 个字段的数据:{source_id (int), user_action (int), index (int)}

问题
我正在寻找一种优雅且方便的方式来存储这种多样化的数据，以便以后可以轻松查询它。
我能想到的替代方案:

• 将每种使用类型的不同数据存储为 JSON/XML 对象的一个​​字段，但提取数据并为这些字段编写查询将非常困难

• 每条记录多了N个数据字段，但是看起来很浪费。

对这种模型有什么想法吗？也许像谷歌分析？请指教...

技术: 数据库是在 phpMyAdmin 下运行的 MySQL。

免责声明:有一个 similar post ，这引起了我的注意，例如 DeskMetrics和 Tracker bird ，或尝试将 google analytics 嵌入到 C++ 原生应用程序中，但我更愿意自己提供服务，并且更好地理解如何设计这种模型。

谢谢!

Answer 1

这看起来像一个 database normalization问题。

我还假设您还有一个名为 events 的表，其中将存储所有事件。

此外，我假设您必须具有以下数据属性(为简单起见):window_name、source_id、user_action、index

为了实现规范化，我们需要下表:

events
data_attributes
attribute_types

这是每个表的结构:

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

我们的想法是，您必须使用所有可能的类型填充 attribute_types。然后，对于每个新事件，您将在 events 表中添加一个条目，并在 data_attributes 表中添加相应的条目，以将该事件映射到一个或多个具有适当属性类型的属性类型值(value)观。

例子:

"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}

将表示为:

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

要为该数据编写查询，您可以使用 COALESCE MySQL 中的函数为您获取值，而无需检查哪些列为 NULL。

这是我破解的一个简单示例:

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

产生以下输出:

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