Designing database: split into different tables or group them together?
countries > systems > levels > subjects
In every subject category, user access the same type of data e.g. user can post and answer a thread, can reply to each answer accordingly. Users can search for topics with particular strings ( Just like how stackoverflow or quora works) and 80% of the time they will only search for topics that are relevant to their subjects. That means highly unlikely they are going to access other country, or other system or other level but I want to keep that functionality as well.
I am thinking whether should I follow the standard database design and separate the country, system, level and subject into separate tables and use their primary key as foreign key to a particular forum thread. But my concern is performance issue, since there will be heavy usage of searching for particular topic from the users when they key in strings. Imagine every search will have to trigger the JOIN functionality.
However I did some research and found that the accepted answer here https://stackoverflow.com/questions/8479252/database-design-3-types-of-users-separate-or-one-table suggested that if the forum thread has the same attributes, it is best to group them into one table together. However, it will have performance issue too when it comes to search functionality. Every search will have to traverse the database row by row, and there are rows that are irrelevant in terms of the country, system, level and subject attribute. Imagine the thread table rows grew to hundred thousands.
What would be the best way to go? Or should I consider NoSQL? I am looking to integrate the DB into AWS backend