Single table with more columns vs multiple tables with fewer columns

Question

What would be a better database design for a social network website? A single table with more columns and fewer rows, or multiple tables with fewer columns but more rows?

For example: A user can post an update on their wall or in a group.

Two database designs I can think of are:

Design 1

UserPosts

• id
• userId
• post
• datetime

UserGroupPost:

• id
• groupId
• userId
• post
• datetime

Potential problem : Might require joins, which can (in future) be a slow query.

Design 2

Posts:

• id
• userId
• groupId
• post
• datetime (where groupid would be null if user posts on their wall)

Potential Problem : Looping over large dataset could take a (long)time.

---

How can I get better performance when data increases? Is there any other (better) way?

Answer 1

My inclination here would always be design option 1, or at least along those lines. Do not worry too much about trying to eliminate the need to join tables in future queries - any normalized database is going to use joins in any useful queries, that's just relational databases.

Also, why would you necessarily have to join the userPosts and userGroupPosts tables for your website? Would they not be displayed separately? The only reason you would join these tables is maybe when searching for posts, but it shouldn't be too hard to write efficient queries for that. Apart from that you may end up wanting to query the tables for analysis purposes, but that is not the primary purpose of this database.

Design 2 could at the least mean that you end up with a very busy table.

The best option though would be to prototype each and run some tests. Build a prototype of each design option, and do some performance benchmarking on different operations with some dummy data.

Answer 2

For me, as per your current structure Design 2 is better. You can implement partitioning, optimized query & structured way to create database/table will decrease the execution time. But some case normalization works better, but totally depends on your database designing architecture.