[SQL] How to solve the old bool attributes vs pivoting issue?
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? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to solve the old bool attributes vs pivoting issue?
On Jun 27, 2012, at 21:07, Andreas 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to solve the old bool attributes vs pivoting issue?
On Wed, Jun 27, 2012 at 7:26 PM, David Johnston wrote: > On Jun 27, 2012, at 21:07, Andreas 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
