CODEWITHSUNDEEP
mysql
Maneesha Shetty
Maneesha Shetty

Reputation: 29

Is it good to have a table with more rows or more tables with less rows in a database?

I am building a database for my application using Mysql, contains 2 tables in which one table will have user details and other table will have all user's activities(say posts,comments,..). I have 2 approaches for this PS.

  1. Group all users activities under one table(say useractivities).
  2. Maintain specific activities table for each user(say user1activity,user2activity,...).

If we go with approach 1, it builds time complexity in case of more users. with approach 2, eats up database. which design will show less time and space complexity?

Upvotes: 1

Views: 173

Answers (2)

Haresh Vidja
Haresh Vidja

Reputation: 8496

For better database maintain, you have to go with the first approach because you can normalize data easily.. and the perfect way to manage database structure, Need to take care of below points

  • You have to give proper indexing in user_id field for fast result in join query.
  • In case of large number of records in one table, then you can create another table like user_activities_archive for store old activities. in the regular period, you can move an old record from user_activities to user_activities_archive
  • You can create multiple tables for user_posts, user_comments instead of user_Activities for more splitting data and different structures of the table, for example you can manage replyto_id in the comment table and user_post table might have title field.

In the second approach for cerate tables for each user, there are many limitations like

  • Very hard in case of Table Joining with other tables
  • In case of fetch all user's activity records, you cant do it.
  • A number of the user base of your application.
  • Limitation of a number of tables in the database.
  • Create more complexity in edit update or delete user records.
  • If the user is not active (just registered) then separate user table useless.

Upvotes: 1

Bommarn
Bommarn

Reputation: 69

As juergen d mentioned in the comment, approach 2 should not be used. However I would consider splitting useractivities into different tables if the possible user activites are different from each other to avoid unneccessary column.

Example: A comment table with information about who made the comment (foreign key to user table) and the comment itself. + A foreign key to another user activity to wich the comment was made.

The comment column in the above table does not make sence for say, just a like of a post, so I would have created a different table for likes.

Upvotes: 0

Related Questions