>>>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? 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).
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).
Thank you,
Poul