Skip site navigation (1) Skip section navigation (2)

Schema design question

From: Ben <bench(at)silentmedia(dot)com>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Schema design question
Date: 2008-03-28 15:55:00
Message-ID: 22E62021-EF4A-44CA-AE7F-5C05FF3FD1E2@silentmedia.com (view raw or flat)
Thread:
Lists: pgsql-general
I'm working on a project which requires me to keep track of objects,  
each of which can have an arbitrary number of attributes. Although  
there will be many attributes that an object can have, the data types  
of those attributes won't be all that varried (int, float, text,  
boolean, date, etc.). My question is: what tradeoffs have you seen in  
picking a solution to this problem?

In other words:

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 text);

(everything smashed down to text by the application)

versus:

create table attrs (id serial primary key, name text);
create table obj (id serial primary key, name text);
create table att_int (oid int references obj.id, aid int references  
attrs.id, value int);
create table att_float (oid int references obj.id, aid int references  
attrs.id, value float);
create table att_text (oid int references obj.id, aid int references  
attrs.id, value text);
create table att_bool (oid int references obj.id, aid int references  
attrs.id, value boolean);
create table att_date (oid int references obj.id, aid int references  
attrs.id, value date);

(everything kept distinct, probably with the application using stored  
procs)

or even versus:

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);

(the worst or the best of both worlds?)

The first certainly seems simplest, while the second seems like it  
offers much better ability for constraint checking and probably more  
compact storage. The second also seems like a perfect example for  
inheritence, except that I don't believe inheritence allows for some  
usuful things like uniqueness, so if I wanted to make my primary key  
in the att_* tables
(oid,aid), then I dont' think I could use inheritence.

The third option doesn't seem all that different than the second  
option to me, though maybe it has benefits I'm not seeing.


Anyway, this seems like a common problem without a perfect solution,  
and I'm sure people must have hindsight opinions on how they solved  
it. Your thoughts?

Responses

pgsql-general by date

Next:From: Stanislav RaskinDate: 2008-03-28 15:56:25
Subject: Re: SELECT DISTINCT ON and ORDER BY
Previous:From: Richard HuxtonDate: 2008-03-28 15:47:15
Subject: Re: creating a trigger to access another postgres database?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group