Re: Should I use one or two tables for summary data?

Victor Porton wrote:

> I need to store data grouped by days and by producers and also summary
> data, that is the result of SUM() (for all producers) grouped by days.
>
> Should I make two tables (one for producers data and one for summary one)
> or one table with `ProducerID IS NULL` indicating the summary data (sum
> for all producers)?
>
> Don't advise not to store the summary data and use SUM() instead, because
> I want it to be fast in retrieval.

Absent further information, three tables then. If MySQL supported materialized views, I would use those instead because they are much faster. But in the MySQL manual in 2007, one Rafael Palacious suggested to simulate materialized views with temporary tables that are renewed in daily intervals:

<https://dev.mysql.com/doc/refman/5.7/en/create-view.html#c9063>

(Oracle has not managed to make comments to the manual simply referable, but I can read HTML :))

> The data is also grouped by other fields.

This indicates that you want better indexing to hopefully shorten the retrieval time instead.

This kind of analysis is better done with Data Warehousing software, not a DBMS.

-- 
PointedEars

Twitter: _at_PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.