I am struggling to figure out the best design for a Sqlite database that will hold data about Facebook pages so that I can calculate benchmarks for how the average Facebook page performs.
I have an option of a single large table, a few largish tables, or many small tables, and I don't know which is the better choice.
20,000+ Facebook pages. ~50 metrics for each page. I want to store data for every day since July, 2011. A python script runs daily to grab the latest data from the Facebook API and dump it into the database.
I rarely calculate benchmarks on all 20,000 fan pages at once--normally I bucket them by the number of fans they have (1-100K, 100K-1M, 1M+) or the industry they're in (food, movies, celebrities, travel, etc) and then calculate benchmarks on the subgroup.
Generally benchmarks are calculated by averaging all the page results for a given day. The results are normalized on a "per-fan" basis to enable comparing across pages of differing sizes. Here's pseudo-code to calculate a benchmark: for each day within the benchmark range: for each page meeting the benchmark criteria: take the chosen metric and divide it by the number of fans that page had that day calculate the average of all the pages for that particular day output that day's average to JSON for charting
1) Top priority: Simple SQL SELECT statements.
I poke around a lot looking for correlations in the data, so I'm constantly writing adhoc SELECT statements. I'm comfortable joining and unioning if need be, but simpler is always better.
2) Speed of calculating results. This only matters for my ad-hoc queries, and I try to keep those queries fast by limiting them to a small date range and a small bucket of pages. If something looks interesting I expand to larger datasets and let it run in the background so it's fine if it takes an hour.
3) Database storage size. Not too worried about this unless I'm choosing between two valid options and one is 2x the size of the other.
4) Speed of inserts. Shouldn't be an issue since I can wrap everything in a single transaction and run it overnight.
Table Design options:
Option A - Single massive table. It seems ideal, but 20K pages and 700 days results in this table containing 14M rows!
Table: Page-Days (ID INTEGER PRIMARY KEY, PageID INTEGER REFERENCES Page(PageID), date DATE, metric1 INTEGER, metricN INTEGER, metric50 INTEGER)
Option B: One Table per metric - 50 tables, each with 700+ DATE Columns and 20K page rows. This is a lot simpler since most benchmarks will only be joining two tables for 40K total rows. But the column count will get huge.
Table: Metric_name (PageID INTEGER PRIMARY KEY REFERENCES Page(PageID), 2011-07-01 DATE, 2011-07-02 DATE, dayN DATE, etc...)
Option C: One Table per day - 700+ tables, each with 50 Metric columns and 20K page rows. This seems crazy at first, but it avoids massive numbers of columns and rows. My one concern is that while I'm almost always calculating metrics on a per-day basis, meaning I don't need to join across tables to generate a daily average, I do need 100+ days at a time when I'm graphing the daily average, and this becomes impossible to do within the SQL layer, I am forced to do it using a loop in my script.
Table: 2011-07-01 (PageID INTEGER REFERENCES Page(PageID), metric1 INTEGER, metricN INTEGER, metric50 INTEGER)
Which should I choose?