DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Type:Content-Transfer-Encoding:Message-Id:Cc:X-Mailer:From:Subject:Date:To;
	b=TsY4cQKQZvg3glTJFBVH7jo4D623iNjux+7j8vynKRSqnVBwEffJIjFGhhIWKYc3vsrHsw/ujiFy6+WAF+jV7xUTPJ1p6JayH2reD7CnLu1VnxV8jAmyXexBiShOI5bWPp4b5+joCK2cATUDLTEs0wKe12o3c8eYyLbo8m7JCIo=
	;
References: <4FEBAE3D.4030202@gmx.net>
In-Reply-To: <4FEBAE3D.4030202@gmx.net>
Mime-Version: 1.0 (1.0)
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Message-Id: <0FED835B-B0CB-4D1A-9C3D-7BC2F5BAB19B@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: How to solve the old bool attributes vs pivoting issue?
Date: Wed, 27 Jun 2012 22:26:52 -0400
To: Andreas <maps.on@gmx.net>

On Jun 27, 2012, at 21:07, Andreas <maps.on@gmx.net> wrote:

> Hi
>=20
> I do keep a table of objects ... let's say companies.
>=20
> I need to collect flags that express  yes / no / don't know.
>=20
> TRUE / FALSE / NULL   would do.
>=20
>=20
> 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.
>=20
> Solution 2:
> I create a table that holds the flag's names and another one that has 2 fo=
reign 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 a=
dd any number of flags without having to change the table layout.
>=20
> There are drawbacks
> 1)     2 integers as keys would probaply need more space as a boolean colu=
mn.
>       On the other hand lots of boolean-NULL-columns would waste space, to=
o.
> 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 compa=
ny?
>       When I create this view I'would not know how many flags exist at exe=
cution time.
>=20
>=20
> This must be a common issue.
>=20
> Is there a common solution, too?
>=20
>=20

You should look and see whether the hstore contrib module will meet your nee=
ds.

http://www.postgresql.org/docs/9.1/interactive/hstore.html

David J.