Message-ID: <4FEBAE3D.4030202@gmx.net>
Date: Thu, 28 Jun 2012 03:07:09 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
	rv:13.0) Gecko/20120614 Thunderbird/13.0.1
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: How to solve the old bool attributes vs pivoting issue?
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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?