pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to solve the old bool attributes vs pivoting issue?
3+ messages / 3 participants
[nested] [flat]
* How to solve the old bool attributes vs pivoting issue?
@ 2012-06-28 01:07 Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: Andreas @ 2012-06-28 01:07 UTC (permalink / raw)
To: pgsql-sql
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 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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to solve the old bool attributes vs pivoting issue?
@ 2012-06-28 02:26 David Johnston <polobo@yahoo.com>
parent: Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: David Johnston @ 2012-06-28 02:26 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
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 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 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to solve the old bool attributes vs pivoting issue?
@ 2012-06-28 04:16 Samuel Gendler <sgendler@ideasculptor.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Samuel Gendler @ 2012-06-28 04:16 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: Andreas <maps.on@gmx.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-06-28 04:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-28 01:07 How to solve the old bool attributes vs pivoting issue? Andreas <maps.on@gmx.net>
2012-06-28 02:26 ` David Johnston <polobo@yahoo.com>
2012-06-28 04:16 ` Samuel Gendler <sgendler@ideasculptor.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox