Re: Schema design question - Mailing list pgsql-general

From Craig Ringer
Subject Re: Schema design question
Date
Msg-id 47ED300E.2090302@postnewspapers.com.au
Whole thread Raw
In response to Schema design question  (Ben <bench@silentmedia.com>)
List pgsql-general
Ben wrote:

> create table attrs (id serial primary key, name text);
> create table obj (id serial primary key, name text);
> create table att (oid int references obj.id, aid int references attrs.id,
>     value_int int, value_float float, value_text text, value_bool bool,
> value_date date);

I think I saw mention here of the DB using a bitmap in the tuple header
to avoid storing NULL fields.

If that's the case (don't trust my word on it), then combined with a
CHECK constraint that ensures that at most one of your typed fields may
be not null, this option might at least prove to be the most efficient.
However, it won't be fun to query.

Storing them all as text won't be much fun to query, which I'd consider
another argument for the many-types tuple. '2' > '11' =  't',  '002' <>
'2', etc.

--
Craig Ringer

pgsql-general by date:

Previous
From: "Teemu Juntunen, e-ngine"
Date:
Subject: VS: Delete after trigger fixing the key of row numbers
Next
From: "Pavel Stehule"
Date:
Subject: Re: PL/pgSQL Documentation, biblio, etc