There are Records in the database which can have Likes and can have Comments. It is necessary to display a list of entries and for each entry to display the number of Likes and Comments.

database structure

Actually, how to do it better? I see three options (not counting cache usage options, like Redis):

  1. We request a list of entries and for each entry we request COUNT Likes and Comments. But this is somehow too costly, in my opinion. There will be many requests to the database.

  2. We keep the number of Likes and Comments in the Records table. It is necessary to add additional logic, but I like this option the most.

  3. We create another PostInfo table in which we will store the number of Likes and Comments for a particular Post. When requesting Records do JOIN to this table. So, probably, it will be the most correct, since the Records do not need to know about the number of Likes and Comments.

  • If a user is deleted, does their like remain, or should it be deleted with it? - i-one
  • @ i-one Like it. Yes, and the ability to delete the user is not provided. Only delete the post. - FlappyStack
  • second version with a separate table - etki
  • See how this is implemented on [StackOverflow] [1]. Here you can submit requests. [1]: data.stackexchange.com/stackoverflow/query/new - Tema_Bel

3 answers 3

In SqlServer, option 1 can be implemented using the materialized view , in which case it will cost up to the third option.

Create a view:

create view dbo.LikeCount with schemabinding as select post_id, Cnt = count_big(*) from dbo.Like group by post_id GO

Add an index that materializes the view:

 create unique clustered index IX_LikeCount on dbo.LikeCount (post_id) GO

You can create a similar view for the Comment table. Then you can request the data as follows:

 select p.id, p.title, isnull(lc.Cnt, 0) as LikeCnt, isnull(cc.Cnt, 0) as CommentCnt from Post p left join LikeCount lc with (noexpand) on lc.post_id = p.id left join CommentCount cc with (noexpand) on cc.post_id = p.id

The convenience of this option is that when data changes in the Comment and Like tables, the numbers will be automatically recalculated (in options 2 and 3, this logic will have to be implemented specifically - in triggers, or procedures / queries that work with adding / deleting likes and comments).

The disadvantage is that there are separate submissions for likes and comments, as a result of which there are two additional connections in the request (in option 3 it will be one, and in option 2 it will not be at all). Also, if there is a possibility that in the future it may be necessary to delete users, leaving likes, then this option will not work, and it is better to look towards options 2 and 3.

Option 2 is more profitable for select (the request does not require an additional connection to pull out the number of likes and comments). Option 3 is better from the point of view of the independence of posts from the quantities accompanying them (if there is a change in the entry corresponding to some post, and at the same time someone decided to like this entry, then these two actions will not block each other).

    In my opinion, we must proceed from the use of:

    • it is better to store in a normalized scheme, i.e. 1st option;
    • read faster from a single place: to post + counters (option 2).

    The minus of the second option is that the calculated data is stored for all records, while only the tip of the iceberg is really required - the latest records that are read / requested.

    Therefore, the solution is a cache , the same Redis, in which to keep records + counters, and in the database - only data on the 1st option.

    In the cache, limit the lifetime of the record. When reading, search in the cache, if not, then pull the database and write to the cache. With the new likes / comments update the database and cache, if the cache entry is still present.

      In the second and third case, you risk getting a cheerful rake with the synchronization of all these counters. As long as you are not Facebook, the simplest option will be 1.

      • If you keep the likes themselves in a separate table and synchronize the numbers to the users table with triggers, then there will be no problems with synchronization. - Mike