From: Andreas <maps.on@gmx.net>
To: pgsql-sql@postgresql.org
Subject: How to solve the old bool attributes vs pivoting issue?
Date: Thu, 28 Jun 2012 03:07:09 +0200
Message-ID: <4FEBAE3D.4030202@gmx.net> (raw)
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maps.on@gmx.net
Subject: Re: How to solve the old bool attributes vs pivoting issue?
In-Reply-To: <4FEBAE3D.4030202@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox