Re: Best practices for aggregate table design - Mailing list pgsql-general

From David G. Johnston
Subject Re: Best practices for aggregate table design
Date
Msg-id CAKFQuwbhoEd_-__cZQXNHG-bBD0o9HPJj2W8ci3rJry-vvTmmA@mail.gmail.com
Whole thread Raw
In response to Re: Best practices for aggregate table design  (John R Pierce <pierce@hogranch.com>)
Responses Re: Best practices for aggregate table design
List pgsql-general
On Tue, Oct 6, 2015 at 2:34 PM, John R Pierce <pierce@hogranch.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.

 

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: Best practices for aggregate table design
Next
From: droberts
Date:
Subject: Re: Best practices for aggregate table design