Re: table count limitation - Mailing list pgsql-general

Marcin ..

Marcin Inkielman wrote:

> 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/...
> > )

Yes - apologies!

>
>
> > 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?

My thinking there was that you would use whatever datatype was appropriate, and I'd
guess that most of them would be character fields, except for date information. So
descrip1 could be used for "Make" for cars, appliances, camera's, lenses, etc.; or
for "Fabric" for clothing, linens, antique garments etc. Wether you want to use
numbers to encode this information, or descriptive text, is up to you.

> > The difficult questions to answer are: How many types of products?
>
> That's exactly I don't know! I hope to to exceed 10000 ;)

That's why I think a single table, with appropriate fields will be more powerful and
flexible. <g>

> > 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... ;)

Good, forward thinking -- a little pain now for a lot of gain later on.

> > 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.

That's really the structure we're imposing here, isn't it.

> > 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! ;))

Well, I'm pretty fond of her!

Later - Miles