Re: table count limitation
От | Yury Don |
---|---|
Тема | Re: table count limitation |
Дата | |
Msg-id | 39A9F224.CBF607E7@vpcit.ru обсуждение исходный текст |
Ответ на | Re: table count limitation (Marcin Inkielman <marn@wsisiz.edu.pl>) |
Список | 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
В списке pgsql-general по дате отправления: