8

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?

  • For me, few columns more rows. It is easy to manage a portion by portion than having a large dataset. If your big concern is the large data in the future, dont. Sql server is designed with that kind of problem, all you have to do is design it properly. Having a large dataset is not a problem if you know how to optimize your query – Vincent Dagpin Feb 2 '16 at 6:46
  • Using execution plan is really a big help. It tells you what is the problem with your query. Ps: dont do loop, if possible use bulk processing, that feature is there already, use it – Vincent Dagpin Feb 2 '16 at 6:48
2

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.

-3

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.

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.