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

[Quoted] J.O. Aho wrote:

> On 02/06/17 16:51, Victor Porton wrote:

>> Please advise:
>> 
>> 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)?

No: SUM() is a slow operation. I need to cache it as a hardcoded value in a column of a table.

Sorry, that I forgot to note that I need this optimization (some would call it denormalization).

Please provide amended answer taking into account that SUM() cannot be used in this way.

> Keep a good index should make it fast when you want to summarize it.
>
> This gives you the most flexibility.
>
>
> If you prompt want to keeps aggregated data, then keep it in a separate
> table, don't forget to keep track of how you group the data if you have
> more than one way to group it.

[Quoted] WHY do you suggest to split this data into more than one table (rather than using NULL as the sign of aggregated data)?

I got your opinion, but I want to know the reasons behind.

> This may require a third table with the grouping options and a fourth
> which links the second table with the grouping options in table three.

-- 
Victor Porton - http://portonvictor.org