Mike -
I did a database once that had about 30 sets of three similar tables that
was used to produce a book. Each set of three tables produced a different
section of the book. These tables were not _exactly_ the same but similar.
For each set of three tables, I had a data-entry form, a proofing report, a
three-table view, a special view for data exporting, and some other stuff.
I was very careful to use special naming conventions for each object - for
example, the default form and report were named the same as the main table
in the set of three tables. I created a control table just to manage things
like the default sort order for each table and report, the name of the menu
block and application block for this section, the name of the program that
would run the Ventura output files, and so on.
The largest table has only about 7,000 rows, but there are 250+ tables in
the database.
Would I do this design this way again? Well, I don't think I have any
alternative since the variable fields in each section are really unique and
the final printed output differs dramatically in some cases. HOWEVER, I
will stipulate that it was the MOST TEDIOUS and time consuming project I've
ever encountered. I cannot imagine having 700 distinct tables if there is
any other possible way to manage the data structure! I spent quite a bit of
time preparing checklists for each of the 30 sections to confirm that I had
installed some change or process on each one.
And, to boot - in the DOS version (where my application was originally
created) you could search for and edit info in forms and reports much easier
than you can in the Windows version. I could find all instances of a pop-up
to a specific table, for instance and change the sort order on the pop-up
right in the sys_forms table - can't do those kinds of things in the Windows
forms. You, my friend would have to open, edit and save each form .... one
at a time ... it gives me a headache just remembering it.
Just my 50 cents worth!
Sami
-----------------------------------------------------------
Sami Aaron
Software Management Specialists
13214 W. 62nd Terr, #139
Shawnee KS 66216
913-915-1971
http://www.softwaremgmt.com
----- Original Message -----
From: "Lawrence Lustig" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, September 26, 2001 11:24 AM
Subject: RE: Database limits
> > 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
>
>
>
