Re: table count limitation

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

In response to

Browse pgsql-general by date

  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?