Re: table count limitation - Mailing list 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