Poul Jensen wrote:
you want to create 1 million tables, all with one of
2 schemas?
I started out with a schema for each file, thinking I could utilize
the schema
structure in queries, but I don't see how. Schemas are useful for
grouping
tables according to users/owners. Other than that, do they add anything
but a dot in the table name?
The word "schema" is a bit overloaded--here it means "table
specification" rather than "namespace".
A general rule of database design is: if you end up creating multiple
tables with the same schema, you're doing it wrong--they should all be
one table with an additional key (usually indexed) to distinguish the
data.
-Doug
Thank you for clarifying -that had me confused!
Would the reason for this general rule be that if you combine same-schema
tables, you can access them all with one query?
No. It is so that the single query becomes manageable. It also greatly
simplifies database maintenance.
At least that would be the
motivation in my case, but it isn't free. It would cost space (500
million entries
for that extra key, add index) and query time (1st condition need now be
checked on 500 million entries of which "only" the 500,000 unique ones
should have to be checked - indexing helps, but there must be a loss).
I suppose how the data is distributed in the table. For the record, I
don't think that 500M records should be that problematic.
Now....
Suppose you need to further partition this (best to wait until 8.1 for
performance reasons), but you could partition it into, say, 10
subtables. You could use inheritance and check constraints to make this
transparent to the application and perform reasonably well.
If this is really necessary it's because it's so unbelievably hard to
access
many tables in one query that I'm thinking the developers maintain
restrictions
on query options in order to force certain database designs - your
general rule
above. If so, what is the advantage? I see only disadvantages (a pain
to be
honest).
Look into inheritance. It makes this easier. However, I don't care
which RDBMS you use, management of 1000 identical tables is going to be
a real pain and I think that everyone here will probably suggest that it
is not exactly a sane thing to do.
Best Wishes,
Chris Travers
Metatron Technology COnsulting
begin:vcard
fn:Chris Travers
n:Travers;Chris
email;internet:chris@xxxxxxxxxxxxxxxx
x-mozilla-html:FALSE
version:2.1
end:vcard
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
