Re: table count limitation

Be aware of one thing: having a seperate table for each product type makes
it extremely difficult to scale your application. In your plan, everytime
you add a new product type you have to create a new table. Which means if
you have any sort of general "search all product types for this
attribute" type search, you have to rewrite the sql for that search every
time, etc. Of course with postgres you can get around some of this by
making a root_object type table with attributes that all products share
and then have your individual tables inherit from it so you can at least
user postgres's inheritance to help your searches.

I still think that finding some way to do this with fewer tables is your
best bet, but I don't know the specifics of your situation.

-----------------------------------------
Water overcomes the stone;
Without substance it requires no opening;
This is the benefit of taking no action.
Lao-Tse

Brian Knox
Senior Systems Engineer
brian(at)govshops(dot)com

On Sun, 27 Aug 2000, 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/...
> > )
>
> > 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. Ld (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
>
>
>