I have learnt that if there are more columns in table, then get those results using join query.
How to decide the max number of columns in table?
How feasible is “Join Operation” if too many table involved?
And if join operation hurts performance
Can we define all columns in one table to get rid of join operation?
I found many developer telling me that its better to have few extra columns if needed frequently in most database operation than to have multiple tables.
8
The golden rule for software design: it depends.
The trouble with trying to evaluate it the way you are doing it is that there is insufficient information. What I have seen work well is to focus on just having a good normalized design (http://en.wikipedia.org/wiki/Database_normalization) and use appropriate indexes for performance, then de-normalize as a last resort as you find performance problems.
For example, our DBA found that after scaling our app up after a decade, a lot of the database’s time was spent JOINing to additional tables just to get a single column. Duplicating a couple of key columns into a couple tables (I think he used a trigger to maintain it, but not sure, wasn’t my area) to eliminate a few frequently used JOINs helped improve performance. It wasn’t a huge deal, but imagine if 90% of your queries got 10% faster. That could be worth it.
Again, this was after 10 years of observing and optimizing. Don’t jump in to this sort of optimization. Just focus on normalization until you find a compelling reason to de-normalize.
This trade-off you are looking for is way too case-by-case. There is no specific maximum # of columns that I can tell you is too many.
1