Re: split up tables or one big one?

Patrick,

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

Patrick Goodwill wrote:

> Hi!
>
> I'm writing a system which i could logically separate it into hundreds,
> perhaps thousands, of tables, or it could put it all into one big table.
> Since each tables could probably only grow to 10s of MBs in size, from a
> design, speed, and scalability perspective, is it perferable to split up
> the tables (as I've currently programmed it) or to put everything into one
> gigantic, multi-GB table? This is for the web, so transaction speed is
> important.
>
> Patrick.