> I thought about this but the table would end up with
> millions of rows of data
> and the only useful index would have at least 700
> duplicates and I am not
> sure how efficient that would be.
Mike:
There's a saying in programming: "Premature
optimization is the root of all evil". Make up a test
database and see what the response is like. Don't
make a messy database design unless you _know_ that
you can't write your project with a clean and easily
maintainable design. Also, don't let your design be
too heavily influenced by what the client's data looks
like on paper, or any limitations they may have run
across trying to represent the in Excel or Access.
I have a database with a large 1.5 million row table
against which complex GROUP BY selects and JOINS are
done in Oterro, and the response is perfectly
acceptable for on-line querying.
As for your indexing described above, it seems to me
that if each row belongs to only one "subgroup" and
each row has a unique column value within that
subgroup, then a key built on those two columns will
uniquely identify the row.
> Speed is going to be important. Every day there are
> around 120 complex
> calculations made for each table with the results
> being stored in the table. The
> report analysis is a whole other very complex
> operation.
Consider the possiblity of holding all the data in one
table and then, if the calculations in the one table
are too slow, doing the following:
1) Identify the subset of records for the calculation.
2) Put them in a temporary table.
3) Perform the calculations.
4) Update the master table.
> I guess at this point I could ask what is the
> maximum number of rows that a
> table can accept and at what point does it become
> too unwieldy.?
Putting the rows in a single table will never be
nearly as unwieldy as trying to maintain 700 identical
tables. It might be a little _slower_ in terms of
retrieval, but almost certainly not slower enough to
eliminate the ease of programming.
--
Larry
__________________________________________________
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger.
http://im.yahoo.com
