large vs. many tables

Larry Ciesla of AT&T Network Systems write: >Here is a question for anyone out there with experience with large databases. >I am part of a team designing an OnLine database that must store about 22 >million records that look something like this: > > area_code > telephone_number > name > house_number > street_name > city > state > county > community > other_stuff > . > . > . > > >Anticipating the type of queries and reports we expect our customers to want >to do, several indexes would be placed on the fields shown and on other >fields not shown. The nature of this database is such that somewhere between >three and seven percent of this data will turnover (change) on a daily basis. >These changes will be done in a batch mode, and will consist of inserts, >deletes and updates. If we were to put all this data in a single large table, >given the overhead associated with maintaining the indexes, we are concerned >that the batch processing will take much too long. Since we are at the >beginning of the project, we can entertain alternatives to one large table. >We could spread the data out in a bunch of smaller tables. This data >falls into the following natural organizational patterns: state, county, >city, then community. After much discussion within our group, breaking the >data into tables for each community would be the best fit for how our users >will use the data. The problem (we think) is that, say, for a 3 state area >with 22 million residents, there might be 7000 communities, each of which >would be a seperate table. Does this make sense? Has anyone ever worked >with a database with 7000 tables? Will the overhead associated with >22 million records spread out in 7000 tables be as bad or worse than one >table with 22 million records? I came into my current project after the data architecture was defined (that was a disclaimer), but our system provides for studying and planning for a telephone network. Each time a user want to start a new study, a new informix database in automatically created, along with about 85 tables; within that study the user can create one or more plans. For each plan that is created another approximately 85 tables are created for that plan within the study database. Although it is unusual, up to 100 plans may be created in a study, the result being over 8500 tables in one database. The biggest problem we've had was in dropping a database. There is, at least was under turbo, a problem in dropping a database with many tables, so when a user wants to delete a study our software drops each table in the database before dropping the database. The point is that we, under turbo and now under online, do have databases with hundreds or thousands of tables and, basically, it works. However, our usage of this architecture really can't be justified because our tables are usually small (less than 200 rows), the biggest we get is about 50,000 rows. Our architecture wastes an awful lot of disk space because on our machine a table uses at least 8k and we rarely have 8k of data in a table. My problem coming into the process late in the game is undoing what been done. It is almost inpossible to change our architecture because of all the embedded code at this point. My advise to you is put a lot of effort into making the correct decision. Try it different ways and test the performance with isql. As far as the index maintenance on updates, you might want to drop indexes before updating and then re-establish them after. However, with only 3-7 % turnover, I suspect that you'd be better off doing your updates with the indexes on. Another project related to the one I'm on has just made the decision to break a few of its tables that they consider big into multiple tables out of fear of bad performance. My advise to them was to figure out how big is big in that performance will be degradated (they didn't listen). I'd be interested in hearing from others that have big tables and what their experiences are. Warren J. Saccente Bellcore