One big table or many smaller tables?

Hi, 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? Thank you very much for any advice you can offer. PS: I'm new to the Informix Mailing List, so if this topic has been discussed before, pardon the repitition. /******************************************** * Larry Ciesla * * AT&T Network Systems * * Phone: 708-224-6747 * * E-mail: ciesla@stairs.att.com * ********************************************/