CODEWITHSUNDEEP
mysql
XO39
XO39

Reputation: 483

One large column or many small rows?

In a news feed app, our customer requires to store all the ids of articles that read by a user.

We decided to create a single table for this, but from performance point of view, which of the following is a better approach:

  1. Have one row per user with two fields, a user_id and article_ids, then each time a user read an article, append the id to the article_ids text - using update and concat (we might end up with a huge data in one column).
  2. Have many rows with two columns, user_id and article_id, then each time a user read an article, insert the article_id along with the user_id in as a new record (we might end up with too many rows).

Or if there is a better way, any suggestions are very welcome.

Upvotes: 1

Views: 565

Answers (3)

Shravan40
Shravan40

Reputation: 9888

With the second approach, you can keep track of other things which your client might ask going forward.

  • First, open/read/visit time.
  • Count of a total number of open/read/visit.
  • Last open/read/visit time.

In this approach, you can apply the indexing on article_id later on if required.

Note: As @Arjan said in his answer, with proper indexing there is no such a thing as too many rows.

Upvotes: 3

Arjan
Arjan

Reputation: 9874

Many records, one for each user_id and article_id combination. That's much easier to update (just insert a row, no need to apply logic) and also allows you to get information about articles when you want to list which ones a user has read. You can use a join and retrieve the correct information from the database at once, instead of having to convert a string to ids and then go back to the database to get the additional data.

With proper indexes there's not really such a thing as too many rows.

Upvotes: 1

user9167402
user9167402

Reputation:

Try to split them as much as possible. Your performance will be increased a lot if, because you just have to pick small pieces of your database. If you go for the first option, you have to split it after certain characters to get the information you want. First it is more challenging in programming and if a user has a bad internet connection, the application would be very slow.

Upvotes: 0

Related Questions