Re: table count limitation - Mailing list pgsql-general

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

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