From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | Andreas <maps(dot)on(at)gmx(dot)net> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: How to solve the old bool attributes vs pivoting issue? |
Date: | 2012-06-28 02:26:52 |
Message-ID: | 0FED835B-B0CB-4D1A-9C3D-7BC2F5BAB19B@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Jun 27, 2012, at 21:07, Andreas <maps(dot)on(at)gmx(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Samuel Gendler | 2012-06-28 04:16:21 | Re: How to solve the old bool attributes vs pivoting issue? |
Previous Message | Andreas | 2012-06-28 01:07:09 | How to solve the old bool attributes vs pivoting issue? |