Lot's of tables

heh, kind of reminds me of a system i had to work with around 1998 time frame, it was Oracle DB and thier forms package on the front end.  the diagram was the only info i had, the folks that built it were a total mess to get help from.

that diagram was like a giant 3 foot by 3 foot thing that reminded me of a CPU or other large scale electronics circut layout

one of the best features was how they did customer accounts....  every edit created a new record with a counter updated.

so every lookup took longer and longer and longer as account 1234 might have 30 rows and they did a nested select on max(version)

so when the owners chose to abandom that mess i had the fun of extracting all the valid data to the new system....

and i had to tell the accounting folks i was not able to figure out how to export the old invoice details format, but i did get them a balance fwd that was right!  when they asked why not i showed them the diagram and they started to have much sympathy for my job and were very glad that i could get so much of the data converted right.

should have seen the war-room for that, i took over our conf room for about 6 weeks.

we had stacks and stacks of data and reports printed out to help check things.... and i had about 3 folks helping me as needed

an accountant, a billing/cs rep and an accountant from our parent company who also acted as a pm for the project.

no docs, no specs , 25K billing accounts

well recall when this was, and they did not spec a big db server for that db. so a 1995 ish cpu and more ram ten pc's of that day but not enough for oracle 7.xxx  with like 50 users connected.

and shure have an audit trail / log  but *NOT IN THE MAIN TABLE* use a second logging table to hold the prior history.

so that the PK and the record lookup are clean and simple.  and they did this with several tables.... so every time you opened a customer it took time to find the "current" version of each part of a customers account.

by the time we switched we have like 80,000 account records for 25,000 accounts.

and thats not counting all the other bloated tables like that.

at the end you could count on having a customer on hold for 3-5 minutes per call waiting for the data to show up. and the db server was on the same wired LAN as the csr or tech ....

heck i have a system i am working on right now that's over 50 gigs on disk and has a very large row count on some tables and i never have that kind of problem with normal lookups.

there are one or two reports that take a few minutes, but they are mostly monthly summary reports that are only ran 1-2 times a month.