From: | Marcin Inkielman <marn(at)wsisiz(dot)edu(dot)pl> |
---|---|
To: | Jurgen Defurne <defurnj(at)glo(dot)be> |
Cc: | postgreSQL general mailing list <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: table count limitation |
Date: | 2000-08-27 09:34:51 |
Message-ID: | Pine.LNX.4.21.0008271055380.1024-100000@mi.marnnet |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Nils Zonneveld | 2000-08-27 12:53:16 | Re: FW: Count & Distinct |
Previous Message | Tom Lane | 2000-08-27 07:51:50 | Re: vacuumdb failed |