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