Re: table count limitation - Mailing list pgsql-general

Marcin   ... comments below

Marcin Inkielman wrote:

> On Sun, 27 Aug 2000, Jurgen Defurne wrote:
>
> > Any time your design is heading in this direction, take a good hard look
> at
> > it. Proper organization with the appropriate indexes is the way to go.
> >
> > With tens of hundreds of tables, how will you decide which to use?
> > How will you write your queries? Customize them for the different
> > tables?
> > Will you be generating a lot of data, thereby creating a lot of tables?
> > How
> > long will they take to create and populate?
> >
> > With fewer, large tables you are appending data at the end, and
> > maintaining
> > indexes. An inherently simpler operation. Queries are written to a known
> >
> > design and structure. You will, admittedly, have large index files, but
> > you
> > will not have hundreds to thousands of tables, each with indexes.
> >
> > The Fishcart ecommerce system, which can be implemented in PostgreSQL,
> > has
> > only 20 tables, four of which have any degree of traffic.
> >
> > A proprietary system done in here in Halifax for the employer's
> > association  has about 16 core tables, two of them are regularly updated,
> the rest
> > contain  relatively static information on members, rates, tax rates,
> piers, etc.
> >
> > Rethink your design, talk it over with the fencepost, draw little
> > pictures,  ask "what if", do some rough data storage calculations -- but
> the
> > general rule  of thumb, with proper normalization, is "fewer is better".
> >
> > Regards - Miles Thompson
>
> Thank you for the comments!
>
> 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).

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.

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.

The difficult questions to answer are: How many types of products? 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?

For a car this may mean: make, model, model type, colour, year, price
For a boat: power/sail/other - and then you get into questions which are
particular for each, and can't be answered by a "one kind suits all"  form.
For sail you would have questions of rig, size, construction material, age;
for power length, seats, inboard/outboard/ etc.

Classifications are difficult, as people tend to think of similar things in
different ways.

Don't rule out the possibility of a text field which can be searched once a
primary selection is made. To continue with the car example, once a set of 20
or has been returned, your user may then search for "leather upholstery", but
then at least only 20 text fields are being scanned, not 20,000. If that
expression isn't in the general field, well that's unfortunate.

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.

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

No, and I hope my explaination shows why.

> PS. sorry for my English - I hope I was understood

You're doing great. 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.

Here's another tip: Ask what you want to get FROM the database, and how you
will ask for it. Quite often the range of possible inputs is large and
confusing, but usually we ask for very particular groupings.

Hope this has helped - Miles Thompson

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