Architecture of a database with a lot of records

I have to build a service for different kind of populations. This service contains a web portal and a web service (queried by smartphones) which will contain a lot of records. Each population has its own set of records, few records are shared between populations.

Each population will have its Events, News, Users... Many users will use this service, the database tables (which will contain these events, news and users) will grow quickly. There will be 100 times more selects than inserts in this tables. Finally, I have planned to use MySQL as database engine.

My question is an architecture question :

• Is it better to have common tables for all the populations (a single table news, a single table event... for all the population) and a column permitting to filter ? Or is it better to开发者_StackOverflow have a single database per population (each population its own event table, its own news table...) ?
• How can I deal with shared object if a database-per-population architecture is more optimized ?

Thanks for you hints and recommendations !

kheraud

You can have a single table for each of news, events etc which will contain all population with some column which will differentiate the population and you can apply partition on that column. This way your high frequency selects on population will access only the corresponding partition and not all. Also you can go for partitioned indexing.

you can refer to this for Mysql partitioning http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html

About the shared columns i guess if share percentage is less you can duplicate the rows, else you will end up accessing more than one partition for a query.