From: | Yury Don <yura(at)vpcit(dot)ru> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: table count limitation |
Date: | 2000-08-28 05:01:24 |
Message-ID: | 39A9F224.CBF607E7@vpcit.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Marcin Inkielman wrote:
>
> On Sun, 27 Aug 2000, Jurgen Defurne wrote:
>
> I think You are right in most situations, however do you think that "fewer
> is better" in situation like this:
>
> I have to construct a catalogue of several types of products (10000
> types). Each type is defined by a different set of features.
>
> If I do it like You suggest I have to create a table:
>
> CREATE TABLE products(
> product_id int4,
> product_type_id int4,
> feature_id int4,
> value text/float/...
> )
>
> of course, it is relatively simple to describe any number of products
> having different types in this table.
>
> however... how may I select a set of product having the same type using
> this table EFFECTIVELY. For example:w
> I have to select:
> - all PC with PII/450Mhz and 128MB
> or
> - all red Renault Megane / 1600GL
>
> Note that each product is described by several rows in the table (each
> type of products is characterised by other number of features) and I dont
> have to compare (select) products having other types (i.e. cars and
> computers in 1 querry).
>
> If i could have 10000 tables - one table for each type of products this
> queries would be a lot simplier - don't you think?
>
> PS. sorry for my English - I hope I was understood
>
> --
> Marcin Inkielman
I was in the same situation few years ago and I used something like
this:
Table "types" describes all types
CREATE TABLE types(
type_id int4,
type_name text
)
Table "features" describes all features for every type, including type
(meaning database type - int or float or date or text etc.) in order to
make a check during writing data about products
CREATE TABLE features(
feature_id int4,
type_id int4 references types (type_id),
feature_type text,
feature_name text
)
Table "products" contains all products
CREATE TABLE products(
product_id int4,
product_name text,
type_id int4 references types (type_id)
)
Table "products_features" contains data about values of features of
every product
CREATE TABLE products_features(
product_id int4 references products (product_id),
feature_id int4 references features (feature_id),
value text
)
Such schema requere some triggers and frontend procedures, e.g. to check
correspondence of value in "products_features" and type of this feature
described in "features".
And this schema allows to make a selects like you wrote, for example
- all PC with PII/450Mhz and 128MB
select * from products p, products_features pf1, products_features pf2
where pf1.product_id=p.product_id
and pf2.product_id=p.product_id
and pf1.feature_id=<id of feature "processor type"> and
pf1.value='PII/450'
and pf2.feature_id=<id of feature "memory amount"> and pf2.value='128';
--
Sincerely yours,
Yury
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Mount | 2000-08-28 08:47:02 | Re: jdbc connection refused, please help |
Previous Message | Bruce Momjian | 2000-08-28 04:52:32 | Re: book publishing date? |