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

 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;
 attr_val(def_id, value) - search for given attributes with values;
 attr_val(website_id, def_id, value) - checks, if given site has
 attribue, search by values for given site and attribute;
 attr_val(def_id, website_id) - like above, without value searching;
 attr_val(website_id, value) - search for attributes on given site 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'

 This is common map structure.

 Kind regards,
 Radosław Smogura

 On Mon, 03 Jan 2011 12:26:45 +0100, Thomas Schmidt
 <postgres@stephan.homeunix.net> wrote:
> Hello,
>
> Am 03.01.11 12:11, schrieb Andre Lopes:
>> Hi,
>>
>> I need advise about a database structure. I need to capture data
>> from the
>> web about one specific subject on few specific websites and insert
>> that data
>> to a database. I have done this question here before, but I think I
>> have not
>> explained very well.
>>
>> What I mean with non linear data is the following:
>>
>>      array(
>>            'name' =>  'Don',
>>            'age'  =>  '31'
>>           );
>>
>>
>>      array(
>>            'name' =>  'Peter',
>>            'age'  =>  '28',
>>            'car'  =>  'ford',
>>            'km'   =>  '2000'
>>           );
>>
>> In a specific website search I will store only "name" and "age", and
>> in
>> other website I will store "name", "age", "car" and "km".
>>
>> I don't know If I explain weel my problem. My english is not very
>> good.
>>
> In theory, using a single table having three columns
> (array-id,key,value) will suit your needs.
> However, providing a simple key/value store is not the idea behind
> DBMS like postgres ...
> See:
> http://en.wikipedia.org/wiki/NoSQL
> http://en.wikipedia.org/wiki/Relational_database_management_system
>
> Thomas