There are nine modules of the website and each module has:
1) Location i.e. Region, Country and City
4) User Preferences
5) User Favorites
I generalized these modules (from point 1-5) and added a table WebsiteSection and then created sub tables to save point 1-5 listed above. The design became neat and clean
I am thinking to repeat point 2 to 5 for each module
I am confused if I generalize all modules then even my design became neat and clean and but:
(i) Users load will effect efficiency of data retrieval
(ii) If problem occur in master or detail table then all modules will be down
(iii) SQL query will be so complex
(iv) If tomorrow load become too much and there is a need of hosting different modules on different dedicated servers then will be very difficult to isolate the coded modules
I stuck between these two separate options (option-A & option-B). Please advise whether I go to Option-A or Option-B?
Generally, you start with a clean design and only change it for proven performance. How many users are you expecting? How many preferences/favorites/etc? This will tell you how many rows in the tables. Then you can create a simulated table, added indexes and see what the performance is really like for a query.
I suspect it will be equivalent. Performance shows up in order of magnitude differences. A ninth of the data is just barely one order of magnitude.