SQL model optimization question

Question

I need to keep track of number of "hits" on a particular item in a DB. The thing is that the "hits" should stay unique with a user ID, so if a user hits the item 3 times, it should still count for a hit of 1.

Also, I need to display the total number of hits for a particular item.

Is there a better way than to store each hits for each items by each users in a separate table? Would keeping the user ID in a string separated by commas a better and efficient way?

Answer 1

No, keeping information separated by commas in a database is almost never better and is almost always much, much, much worse than the alternative.

Do you need to keep each individual hit (is there additional information attached to it), or just the count per user? If the first is true, use a table with thing_id and user_id columns (plus columns for the other attributes that apply to the "hit"). If the second is true, use a table with thing_id, user_id, and hit_count columns and use an UPDATE command like:

UPDATE hit_count_table SET hit_count = hit_count + 1
   WHERE user_id = :userid AND thing_id = :thingid;

Answer 2

If you have a set number of items, and the number is small, you could probably get away with keeping a separate "Hit" table for each item.

If you have a lot of items (or an unbounded number of items), I'd probably just have one table to track hits across all items. If you end up with a lot of hit records, you may need to rethink the design, so that you can quickly lookup users, and decide whether you need to insert a hit record.

Overall, you have several options, and they all have performance and convenience tradeoffs. I'd just pick one that you like, and go with it. You'll find out what's good and bad, and you can chalk it up to experience!

You should never store things in a database as "CSV" strings - this is a complete nightmare to query.

Answer 3

Create a new table hits with columns thing_id and user_id. Add a UNIQUE index on thing_id and user_id. Then you can update the table with this query:

INSERT INTO hits (user_id, thing_id) VALUES (?, ?)

Because of the index, that will either add a row or do nothing (if you can you should specify ON CONFLICT IGNORE or equivalent when you define the table, that will avoid errors). You can get the hit count with this query:

SELECT COUNT(*) FROM hits WHERE thing_id = ?