MySQL Forums
Forum List  »  Data Warehouse

Re: Summary Table Design Question
Posted by: Rick James
Date: July 26, 2015 08:36AM

It depends.

My rule of thumb is that a Summary Table should have one-tenth as many rows as the Fact table. (This, of course, can vary widely.) If Day+Company has half as many rows as the Fact table, it is not doing much summarization, and is probably not very efficient. If summarizing only on Day leads to a summary table with 1/1000 the rows, that's great for efficiency, but begs for adding another 'dimension'.

Always approach the Summary table with SUM(sums) and SUM(counts) -- That way a daily Summary table can handle a Weekly report (etc). And it would automatically handle your case of "multiple rows for each day".

Approaching your question from a different angle:
"save data for each country separately" -- It is much better to have a `country_code` column in a single table than having a hundred tables, one per country.

http://mysql.rjweb.org/doc.php/summarytables

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Summary Table Design Question
1306
July 26, 2015 08:36AM
980
July 28, 2015 01:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.