适合于多种数据排列的数据模型设计(RDBMS)

Question

我正在构建一个分析应用程序，我们跟踪公司营销活动的转换。转换是如果他们去超市买产品。如果公司是亨氏公司，他们可能会为不同的产品开展宣传活动，因此，活动可能是：

• 烤豆子
• 番茄汤
• 番茄酱

这些都是在线宣传活动，因此它们可以有不同的媒体，例如：

• 网站
• Facebook页面
• 闪光旗广告
• 移动应用广告

如果有人购买一种产品，它是通过超市购买的，例如：

• 沃尔玛
• 阿斯达
• 萨夫韦
• 克罗格

我们正在追踪所有这些的转换。分析应用程序需要显示上述任何组合的转换数据。所以，例如，我可能需要显示转换..。

• 用来烤豆子。
• Facebook页面上的烤豆子。
• 为超市，沃尔玛，但所有的运动和媒体。
• 对于沃尔玛来说，它是由Facebook页面制作的，但所有的活动都是如此。
• 为凯丘普制作的闪光旗帜广告和安全。

为了使分析更快，我们避免处理原始数据(数百万条记录)，而是每天存储数据的聚合版本。比如说，9月12日，我可以储存我们对烤豆子进行了12次转换，6次转换(针对所有产品)是通过网站进行的，而沃尔玛有8次转换，这些转换可以放在3个不同的表中(称为活动、媒体和超市)。但是，如果我需要知道通过Facebook页面进行的Ketchup和沃尔玛( Walmart )的转换，那么在单独的表中存储显然是行不通的。

我很难想出一个数据模型来支持上面的内容。我使用的是标准关系数据库(MySQL)。也许有一个更好的策略来处理这个问题。

Answer 1

是的，有更好的策略。它被称为维建模，或星型模式。

您可以存储一个名为事实表的表，该表包含用于竞选、媒体和超市的列。

对于每个属性，事实表中的列都是维度表的外键。一个维度表，每个，运动，媒体，超市。

在这种情况下，为了避免数以百万计的行，可以向事实表conversions添加第四列。在得到转换时增加计数(而不是添加另一行)。

下面是一个示例表：

CREATE TABLE FactTable (
  campaign_id INT,
  medium_id INT,
  supermarket_id INT,
  conversions INT,
  PRIMARY KEY (campaign_id, medium_id, supermarket_id),
  FOREIGN KEY (campaign_id) REFERENCES Campaigns(campaign_id),
  FOREIGN KEY (medium_id) REFERENCES Mediums(medium_id),
  FOREIGN KEY (supermarket_id) REFERENCES Supermarkets(supermarket_id)
);

然后，您可以查询所有转换：

• 用来烤豆子。 从FactTable联接活动中选择SUM(转换)使用(campaign_id)；
• Facebook页面上的烤豆子。 选择和(转换)从FactTable加入运动使用(campaign_id)连接媒体使用(medium_id)，其中运动=‘烤豆’和媒体= 'Facebook'；
• 为超市，沃尔玛，但所有的运动和媒体。 选择和(转换)从FactTable加入超市使用(supermarket_id)超市=‘沃尔玛’；
• 对于沃尔玛来说，它是由Facebook页面制作的，但所有的活动都是如此。 从FactTable中选择SUM(转换)，使用(medium_id)加入超市使用(supermarket_id)，其中媒体= 'Facebook‘，超市=’沃尔玛‘；
• 为凯丘普制作的闪光旗帜广告和安全。 从FactTable联合运动中选择SUM(转换)使用(campaign_id)连接媒体使用(medium_id)加入超市使用(supermarket_id)其中运动= 'Ketchup‘和媒体= 'Flash’和超市= 'Safeway'；

有关维度建模的更多信息，请查看拉尔夫·金博尔著。

Answer 2

我认为，通过试图操纵数据结构以避免处理原始数据，您正在增加复杂性，并降低灵活性，但实际上并没有什么好处。有了适当的索引和经过适当调优的查询，查询数百万条记录就会花费很少的时间。我在多个字段中查询了5亿张记录，结果不到20毫秒。

把你的精力放在调优上，而不是设计新的数据结构，当那些使用这些分析的人需要一些稍微不同格式的数据时，你会很感激，这使得你的精心设计过时了。