Implementing comments, comments, views for the product

Question

Im creating an e-commerce backend where each of my products has the following counter attributes

- product views - product likes - product comments count 

The current database columns that I have for the product database table,

  - id - likes_count - views_count - comments_count - category_id - category_parent_id - category_sub_parent_id - handling_charge - shipping_charge - meetup_address - is_additional_fields - status - is_deleted - created_at - updated_at 

As you can see from the next Wanelo engineering blog, the implementation of a counter that can often be updated on one line will lead to blocking the line on innodb, which, if it is updated frequently, can cause deadlock situations in the application. But the solution for this is largely explained on the blog I got an idea about. But what if there are several counters associated with one product that can be updated at the same time as the application grows. How do I create a database table for counters. Do I have to maintain separate tables, namely

 likes counter table - id - product_id - count views counter table - id - product_id - count comments counter table - id - product_id - count 

By maintaining a separate table, even if a simultaneous update for the product (for example, + comment + view), it will be updated separately and reduces the likelihood of row blocking situations. If it is in the same table, and if updates for all of this occur simultaneously, this can cause a problem.

Question: Is there a better way by which I could create tables for the counter? Any suggestions please?

Answer 1

The counter in the product table for presentations is perfect.

A separate table for such columns, for example (product_id, user_id), so each user can only use the product once. Otherwise, they could be kneading, as if it were just a counter.

Separate table for comments with columns such as (product_id, comment_text, date .. etc.)

Is that what you are asking?

Answer 2

Using background queues to buffer inserts / updates, as suggested in the generic link, is pretty standard, and I was going to offer the same thing.

You can recount several counters in the same way as one counter. The number of views can be cached in Memcached / Redis, or you can store them in a separate table (although I would suggest just using some analytics solution for this).

Your worker:

 class ProductCountsWorker # ... def perform(product_id) Product.find(product_id).update_counts! end end 

And in your model:

 class Product < ActiveRecord::Base # ... after_create :init_views_count_buffer private def init_views_count_buffer reset_views_count_buffer(views_count || 0) end def views_count_cache_key "#{cache_key}/views_count_buffer" end def reset_views_count_buffer(value = 0) Rails.cache.set(views_count_cache_key, value) end # Called from controllers etc def increment_views_count_buffer Rails.cache.increment(views_count_cache_key) end def update_counts! transaction do update!( likes_count: likes.count, views_count: views_count + (Rails.cache.fetch(views_count_cache_key) || 0), # Or, if you have a separate views table: # views_count: views.count, comments_count: comments.count, ) reset_views_count_buffer end end end 

Another suggestion would be to split this view counting function into anxiety.

Rails low-level caching documents