8 months ago

DB Design Help - Primary Index : String or Numeric

Posted 8 months ago by thebigk

I'm designing a database where I need to track the activities of a user and then assign some points according to the activity done on the site.

I'll need to identify each activity uniquely for each user so that I can avoid awarding points for the same activity done twice: For example: If user likes / unlikes / likes the same post.

My solution is to create a unique key for each activity and assign it to the user. The key I am thinking of is of following type -

post-like-<post_id>-<user_id> Example: post-like-100-2

After searching a bit, I found out that I could make a primary index on the column that stores keys. But then I also read that instead of creating a string-based key; I could make an integer to represent each activity performed by individual user.

One more approach on my list is to have separate columns and then build a primary index on it like :

k1 | k2 | k_id | u_id

...and then create a unique index on it.

Since we have thousands of users - I'm sure this table's gonna grow large very quickly. I request you to help me figure out the right approach for my DB design.

Please sign in or create an account to participate in this conversation.