Re: Best practices for aggregate table design

On Tue, Oct 6, 2015 at 2:34 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> On 10/6/2015 11:24 AM, droberts wrote:
>
>> OR a dimension 'type' with values outbound/inbound and a single measure
>> column 'total' ?
>>
>
> that smells a bit too much like an "EAV" (entity-attribute-value) which is
> considered an antipattern in relational circles

​​All models are wrong - but some are useful.

In this example what if you care to distinguish between external and
internal variations of inbound/outbound. i.e., Inter-office calls.
​
​While this could degrade to EAV used in moderation its called
normalization. Think about the best way to record phone numbers for a
person. This is similar.

The more arbitrary the distinction the more trouble you can get into with
using columns instead of rows. Typically if its simply "one, two, three"
you are asking to get hosed. When it is more attribute based you are
generally safe in-so-far as useful questions that your model is expected to
answer can be answered with a minimum of difficulty. And if you end up
having to answer different questions than designed for the choice of
row-vs-column probably will be the smallest of the changes that need to be
made.

In a data warehouse situation the choice should also be informed but how
the source system models the data in question. Which itself is based upon
the real-world applicability of the model as it pertains to the users of
the system.

David J.