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@xxxxxxxxxxxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
