I need your expert opinion here. I am deveoping a site and its user profile has about 80 fields like, name, email, contact, website, favorrites blah blah blah.. now, this website will be serving about a million users in near future.. so my quesiton is, Will it be a good idea to keep all these fields in a single table say "users" table, OR divide all these fields in many different tables, as I see if I divide these fields in different tables, it will be helping while fetching that information (for example searching, display of profiles) and will not put much load on server, on the other end, if I divide into more tables, the more quries will be executed to fetch a single user's iformation and thus the more load on server, what would you suggest ? what will be the better option to proceed with ? regards
Depends. In the case of a forum like this one, I might suggest using two tables, one for basic info, like the info needed for the mini profile on the top of posts or the member list page and then an extended version when users view other profiles, or view their user control panel. Whether or not to include the basic info in the extended version is another question. Basically, there are many factors, consider how often certain fields are needed / not needed for different pages and how often these pages are accessed etc. From what I can tell from your description, it looks like it could do with at least a split for basic/extended info as 80 fields is very high.