>> I've got a Firebird database design that we've used for a few years.
>> It essentially holds large e-mail archives on a per-user basis.
>> Currently we have a single table that stores each message in a row, and
>> a field identifies the user. Most of the fields are indexed too.
>>
>> The problem is that queries quickly become very slow as the data volume
>> increases. It seems to me that it would make more sense to have a
>> single table per user because there are no queries that span multiple
>> users.
>>
>> Does it make sense to do this if it results in several thousand tables
>> or are there disadvantages in having many tables? I know that it is
>> bad database design, but is there a practical reason in this case to
>> uses a single table?
>>
>> Thanks for your advice,
>> - Elric
>
> I see no reason to maintain thousands of tables. Query peformance might
> depend on your queries and the indexes you have so you'd best show some
> more
> info on this subject.

One thing to keep in mind when using a large number of tables and fields:
use DOMAINs instead of plain field definitions. Metadata queries (also
done by the engine itself) become slow when there's tons of field
definitions.
Using domains for your datatypes differs a lot in this regard.


With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com