Thread: How to solve the old bool attributes vs pivoting issue?

How to solve the old bool attributes vs pivoting issue?

From
Andreas
Date:
Hi

I do keep a table of objects ... let's say companies.

I need to collect flags that express  yes / no / don't know.

TRUE / FALSE / NULL   would do.


Solution 1:
I have a boolean column for every flag within the companies-table.
Whenever I need an additional flag I'll add another column.
This is simple to implement.
On the other hand I'll have lots of attributes that are NULL.

Solution 2:
I create a table that holds the flag's names and another one that has 2 
foreign keys ... let's call it "company_flags".
company_flags references a company and an id in the flags table.
This is a wee bit more effort to implement but I gain the flexibility to 
add any number of flags without having to change the table layout.

There are drawbacks
1)     2 integers as keys would probaply need more space as a boolean 
column.        On the other hand lots of boolean-NULL-columns would waste 
space, too.
2)    Probaply I'll need a report of companies with all their flags.        How would I build a view for this that
showsall flags for any 
 
company?        When I create this view I'would not know how many flags exist 
at execution time.


This must be a common issue.

Is there a common solution, too?




Re: How to solve the old bool attributes vs pivoting issue?

From
David Johnston
Date:
On Jun 27, 2012, at 21:07, Andreas <maps.on@gmx.net> wrote:

> Hi
>
> I do keep a table of objects ... let's say companies.
>
> I need to collect flags that express  yes / no / don't know.
>
> TRUE / FALSE / NULL   would do.
>
>
> Solution 1:
> I have a boolean column for every flag within the companies-table.
> Whenever I need an additional flag I'll add another column.
> This is simple to implement.
> On the other hand I'll have lots of attributes that are NULL.
>
> Solution 2:
> I create a table that holds the flag's names and another one that has 2 foreign keys ... let's call it
"company_flags".
> company_flags references a company and an id in the flags table.
> This is a wee bit more effort to implement but I gain the flexibility to add any number of flags without having to
changethe table layout. 
>
> There are drawbacks
> 1)     2 integers as keys would probaply need more space as a boolean column.
>       On the other hand lots of boolean-NULL-columns would waste space, too.
> 2)    Probaply I'll need a report of companies with all their flags.
>       How would I build a view for this that shows all flags for any company?
>       When I create this view I'would not know how many flags exist at execution time.
>
>
> This must be a common issue.
>
> Is there a common solution, too?
>
>

You should look and see whether the hstore contrib module will meet your needs.

http://www.postgresql.org/docs/9.1/interactive/hstore.html

David J.


Re: How to solve the old bool attributes vs pivoting issue?

From
Samuel Gendler
Date:


On Wed, Jun 27, 2012 at 7:26 PM, David Johnston <polobo@yahoo.com> wrote:
On Jun 27, 2012, at 21:07, Andreas <maps.on@gmx.net> wrote:


You should look and see whether the hstore contrib module will meet your needs.

http://www.postgresql.org/docs/9.1/interactive/hstore.html


hstore is certainly an option, as are the crosstab functions in the tablefunc contrib module, which can definitely do what you are looking for.  The 2-query form of the function would allow you to generate a resultset that gives true/false/null for every possible attribute without having to have a column for every attribute, yet you'd have all of the convenience of a normalized schema representing attributes when doing other queries. There are a fair number of threads about using the crosstab functions in the archives of this list.

--sam