MySQL Database Structure - Storing Facebook FQL Results

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开发者_如何学Go_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:

1. Get FQL result.
2. If it's an empty Array, do nothing (because there is no data).
3. 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).
4. 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: http://i.stack.imgur.com/0fekJ.png

Can anyone please provide feedback? Is this a good way to go about it, or are there better options?

Thanks in advance! :)

---

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.