Re: table count limitation - Mailing list pgsql-general

On Sun, 27 Aug 2000, Miles Thompson wrote:
>
> Each table you would create for a different type of product can be replaced
> with a single column, say "product_type", which you could use to broadly
> classify your items: cars, boats, computers, appliances, etc.

don't you think that this is exectly the 2nd column in my table I
previousely defined?

> CREATE TABLE products(
> product_id int4,
> product_type_id int4,
> feature_id int4,
> value text/float/...
> )

> Other columns could simply be labelled "descrip1", "descrip2", "descrip3", as
> many as you need, for the different attributes of each item. So "descrip1" may
> contain what you may describe as the brand, or the make, or the model, or the
> processor type of an item in your user interface.

that's a possibility, however remember that I don't know right now how
many descriptors I will have for each product type, and how many types I
will have. Also, if I use a general form for a descriptor I will have to
chose a text field and it is not very effective to examine that type of
fields if they contains numbers, dates, etc... Indexes put on the
descriptor columns wont be very effective as one column will describe
different products features - dont you think?

> The difficult questions to answer are: How many types of products?

That's exactly I don't know! I hope to to exceed 10000 ;)

> How many
> meaningful attributes for each? Base your table design on the answers, and I'd
> bet you start to run out at about 6 or so. How fine grained do you want to
> make your search?

I agreed. 6 up to 10 should suffice.

> You could take this further, and have a "product_type" table. For each major
> item, you would have a standard set of attributes you could use to populate
> pick lists, so that users would not ask for porcelain sailboats with PIII
> processors of the Ming dynasty. These same lists would be used in the
> maintenace forms you use to add items, so you have a consistency.

in fact, the problem I presented is only a part of bigger one... I have to
construct a full system of product classification. Of course I will have
to construct a table of product types and a table of product type
descriptors.

> This table could also contain the labels, the applicable descriptions for each
> product_type. When a user selected a particular product you could fetch the
> descriptive text for each field and load it into your interface as the
> descriptor for the field.

that is exactly my system is doing now... ;)

> Contact managers do this all the time - the "user
> definable" fields have standard names and the user just supplies the label.
>
> This saves a lot of work - one or two standard forms displayed with "custom"
> labels, standard queries which run when you click on "Submit", and standard
> results forms. (I'm calling them forms, they could be web pages.) Even
> background colours and logos could change according to product type.
>
> > If i could have 10000 tables - one table for each type of products this
> > queries would be a lot simplier - don't you think?

My question sent to the mailing list was if it is possible to work with
postgres+a lot of tables. In fact I am planning to use this tables only
for selects purpose just like multi-cross tables in M$ Access and
internally to use a table of product types, a table of descriptors of
product types (feature vectors) and table of products as defined above.

> You're doing great.
Thx ;))
> I had a siimlar discussion a short while ago with someone
> who wanted to do classified ads for agricultural products: livestock, feed,
> tractors, etc. Thinking out a good design for your database is hard work.

I am not sure if SQL is the best language to use with that types of
problems, I use it because SQL-dbases are the most developped and
reliable, however I think that a hierarchical db should be more
appropriate.

> PS You are in Poland? What part? My wife's grandparents emigrated from around
> Lodz in 1904~06 and settled in Winnipeg, Manitoba in Western Canada. /mt

I live near Warsaw. LÓdź (Lodz - if your e-mail reader do not support
iso-8859-2 ;) ) is ~100km from here. You must be a very happy man if your
wife is partially from Poland. Polish women are really beautifull! ;))

regards,

Marcin Inkielman