Re: Need advise for database structure for non linear data.

From: Thomas Schmidt <postgres(at)stephan(dot)homeunix(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Need advise for database structure for non linear data.
Date: 2011-01-03 11:58:43
Message-ID: 4D21B9F3.7070401@stephan.homeunix.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

Am 03.01.11 12:46, schrieb Radosław Smogura:
> I can propose you something like this:
>
> website(id int, url varchar);
> attr_def (id int, name varchar);
> attr_val (id int, def_id reference attr_def.id, website_id int
> references website.id, value varchar);
> If all of your attributes in website are single valued then you can
> remove id from attr_val and use PK from website_id, def_id.
>
> Depending on your needs one or many from following indexes:
> attr_val(value) - search for attributes with value;
(...)
> Probably you will use 2nd or 3rd index.
>
> Example of search on website
> select d.name, v.value from attre_def d join attr_val v on (v.def_id =
> d.id) join website w on (v.website_id = w.id)
> where d.name = 'xxxx' and w.url='http://somtehing'

Imho its hard - (if not impossible) to recommand a specific database
scheme (incl indexes) without knowing the applications taking plance
behind it.
Your schema is nice for specific querying, but might blow up if lots of
data is stored in the database (joins, index-building might be time
consuming).
On the other hand, google put some effort into their "BigTable"
http://en.wikipedia.org/wiki/BigTable for storing tons of data...

Thus - it all depends on the usage :-)

Thomas

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Trupti Ghate 2011-01-03 12:25:40 Re: [NOVICE] Loading "posgres 9.0" libpq library on Linux version"Red Hat 3.4.6-3"
Previous Message Devrim GÜNDÜZ 2011-01-03 11:58:37 Re: [NOVICE] Loading "posgres 9.0" libpq library on Linux version"Red Hat 3.4.6-3"