1

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?

  • 1
    Have you built a POC and gathered hard data on the performance of the solutions you've thought of? Any deliberate performance optimization without data guiding exactly what's wrong is worthless - you may very well identify a "problem" which you then "fix", only to find the original took 350ms and the optimized takes 347ms, while the part you weren't looking at (because you didn't profile) is doing an extra 150ms of processing. – Delioth Sep 5 at 17:22
  • 1
    There is a chapter about modeling organization structures like this in Fowler's bool "Analysis patterns". Here you find this part online. And don't overthing performance beforehand, your will find out the real issues are at a completely different place. – Doc Brown Sep 7 at 8:13
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.