I am designing the database for a highly content driven, dynamic, location based site.
Whenever a user visits the site, he sees most of the data specific only to his city. There is very little generalized data to be shown.
For this, I have 2 designs in mind :-
(1) Have a single database encompassing all cities
In this case, for every page, I will have to do extra JOINS to pull out data corresponding to a particular city and the tables will become very large.
But then, the database will not have redundancy and will be optimized.
(2) Have 1 common database for tables storing GENERAL INFORMATION and user specific information AND have 1 database PER CITY (city specific databases having the same design). i.e. :-
city3-database…and so on
In this case, the number of JOINS being performed are reduced and wherever they occur, they may be between tables belonging to different databases (is this good ?). But this design introduces redundancy and difficulty in feature scaling.
Please give your views on which of the above 2 designs would be better for this city-specific site. A good reasoning will be very appreciated. [I am using CakePHP as the framework for coding…if this may help]