MySQL 数据库结构 - 存储 Facebook FQL 结果
我使用 FQL 查询 Facebook Insights 表 并将数据返回到我的服务器，我计划将其存储在 MySQL 数据库中。
我发现所有 Insights 指标都会为值字段返回不同的数据类型。例如，像application_installation_adds这样的东西将返回这样的值：
Array
(
[0] => stdClass Object
(
[metric] => application_installation_adds
[value] => 3
)
)
..而像application_permission_views_top这样的指标如果有数据则返回此值：
Array
(
[0] => stdClass Object
(
[metric] => application_permission_views_top
[value] => stdClass Object
(
[permissions_impression_email] => 5
[permissions_impression_user_birthday] => 4
[permissions_impression_read_insights] => 4
)
)
)
..如果它为空，则返回此值：
Array
(
[0] => stdClass Object
(
[metric] => application_permission_views_top
[value] => Array
(
)
)
)
考虑到不同的数据类型和值，我想知道在数据库中存储这些数据的最佳方法是什么。
我正在考虑建立一个像这样的表：
- metric_name
- metric_subname
- value
，然后使用这样的过程：
- 获取 FQL 结果。
- 如果它是一个空数组，则不执行任何操作（因为没有数据）。
- 如果是单个值，请插入 metric_name 和 value。为 metric_subname 指定“Singlular”值（这样我就知道它只是一个单值指标）。
- 如果它是 stdCLass 对象，请使用 foreach 循环填充 metric_subname 列。
这应该给我一个像这样的表格，这将允许我以简单的方式查询数据： https://i.sstatic.net/0fekJ.png
有人可以提供反馈吗？这是一个好方法吗，还是有更好的选择？
提前致谢！ :)
I'm using FQL to query the Facebook Insights table and return the data to my server, where I plan to store it within a MySQL database.
I've found that all the Insights metrics return different data types for the value field. For example, something like application_installation_adds will return a value like this:
Array
(
[0] => stdClass Object
(
[metric] => application_installation_adds
[value] => 3
)
)
..while a metric like application_permission_views_top will return this if it has data:
Array
(
[0] => stdClass Object
(
[metric] => application_permission_views_top
[value] => stdClass Object
(
[permissions_impression_email] => 5
[permissions_impression_user_birthday] => 4
[permissions_impression_read_insights] => 4
)
)
)
..and this if it's empty:
Array
(
[0] => stdClass Object
(
[metric] => application_permission_views_top
[value] => Array
(
)
)
)
Given the different data types and values, I was wondering what the best way to store this data within my database would be.
I was thinking of setting up a table like this:
- metric_name
- metric_subname
- value
and then using a process like this:
- Get FQL result.
- If it's an empty Array, do nothing (because there is no data).
- If it's a single value, insert metric_name and value. Give the metric_subname a value of "Singlular" (so that I know it's just a one-value metric).
- If it's a stdCLass Object, use a foreach loop to fill in the metric_subname column.
This should give me a table like so, which will allow me to query the data in an easy manner down the track: https://i.sstatic.net/0fekJ.png
Can anyone please provide feedback? Is this a good way to go about it, or are there better options?
Thanks in advance! :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您知道指标层次结构可以深入多少层吗？如果超过两个，您的解决方案将无法扩展。
假设在某个点深度将达到> 2、设计通用模式。我想到的一个方法是保留两个表：metrics 和 user_metrics。指标表仅包含可能指标的层次结构，只需进行一项更改：实现 id、name、parent_id，而不是名称和子名称。第二个表将指标与用户相关联，并存储值，因此：metric_id、user_id、value。
Do you know how many levels deep the hierarchy of metrics can go? If more than two, your solution doesn't scale.
Assume at some point depth will reach > 2 and design a generalized schema. One that comes to mind would be to keep two tables: metrics, and user_metrics. The metrics table would just contain the hierarchy of possible metrics, with one change: instead of name and subname, implement id, name, parent_id. The second table would associate a metric with a user, and store the value, so: metric_id, user_id, value.