
Designing database: split into different tables or group them together? - eren_jaeger
I am fairly new to database design and now trying to build my own forum-based application. The app will have nested categories as<p>countries &gt; systems &gt; levels &gt; subjects<p>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.<p>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.<p>However I did some research and found that the accepted answer here https:&#x2F;&#x2F;stackoverflow.com&#x2F;questions&#x2F;8479252&#x2F;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.<p>What would be the best way to go? Or should I consider NoSQL? I am looking to integrate the DB into AWS backend
======
gigatexal
If you go single table you can use filtered indexes if your database supports
it to speed up queries instead of having to do a row by row search with a
where clause.

Personally this seems like a job for DynamoDB but that’s a whole beast unto
itself. You must know the queries you will do to the database before hand as
that guides how you craft it. There’s an amazing talk at reinvent that is
given almost every year on creating a DynamoDB schema
([https://m.youtube.com/watch?v=HaEPXoXVf2k](https://m.youtube.com/watch?v=HaEPXoXVf2k))

I would normalize as the theory says and then denormalize as performance
requires it.

