On Jun 27, 2012, at 21:07, Andreas <maps...@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.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply via email to