Designing database: split into different tables or group them together?

Question

I am fairly new to database design and now trying to build my own forum-based application. The app will have nested categories as

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 (Just like how stackoverflow or quora works). Users can search for topics 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. 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?

Answer 1

A good rule of thumb is to design the database schema without too much regard to optimizing performance (especially if you're new) and then change it, if necessary, for performance reasons. As Delioth points out, you often won't know the real bottlenecks until it's working.

Generally, you are not going to need to change the core table structures to optimize performance - modern relational databases can, with the correct indexes, perform joins and queries very quickly, even over tables with millions of rows. If you are going to focus on something up front - focus on the indexes that will be required by the those common queries and joins.

Answer 2

Generally you should design your data model to match the real-world requirements and not commit premature optimization. Hundreds of thousands of rows are not a challenge for a modern database engine.

Give some thought to what your partition key could be. Partitioning would keep your data logically in one set while allowing the database to optimize for operating on smaller data sets. The concept exists in SQL and NOSQL.

Also, think about your choice of database. An ACID database works hard to keep it's many promises. Do you need all of them, or can you live with an 'eventually consistent' approach (which would lower the resource requirements and help scalability)

Worth reading: Sharding Pinterest: How we scaled our MySQL fleet