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(at)stephan(dot)homeunix(dot)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