From: | Radosław Smogura <rsmogura(at)softperience(dot)eu> |
---|---|
To: | Thomas Schmidt <postgres(at)stephan(dot)homeunix(dot)net> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Need advise for database structure for non linear data. |
Date: | 2011-01-03 11:46:34 |
Message-ID: | 6b1f39d86f3f6787b09e8a81823a5e6c@softperience.pl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Trupti Ghate | 2011-01-03 11:53:14 | Loading "posgres 9.0" libpq library on Linux version"Red Hat 3.4.6-3" |
Previous Message | Thomas Kellerer | 2011-01-03 11:36:41 | Re: Need advise for database structure for non linear data. |