What is the good way store relations like favorite/star on database?

Hello, I have an question.

For example; You can star a repository on GitHub and you can unstar too when you after did. If we use a join table to save this action, what happens after destroy it? I’m curious about what is the optional solution for that. Because if we decide the destroy relation, it’s not good for DB indexes, etc. So we can add to another column for status? This solution any good? Does it matter?

Could you say a little more about the part where destroying a join record is bad for indexes? I’m not sure what you mean by this. Is it because at scale, it takes a long time to recompute the index when you remove a row? Would that not also be true of adding a row?


The last time I looked at implementing this sort of functionality, ISTR using acts_as_votable (GitHub - ryanto/acts_as_votable: Votable ActiveRecord for Rails) for it. If you need any assistance in regards to how to get it working, do reach out. – H

I would make a relation table between my user and my starred element (repository for GitHub).

The absence or existence of this relation could indicate if the repository is faved or not.

If you are worried about inserting/deleting rows, you can always create all the rows for this relation at migration time and use a boolean column to store the state of fav.

I would model a solution exactly as you described it and I wouldn’t add any additional columns for the status. At least not in the first iteration.

When you don’t have to column, checking if a user has faved anything is a matter of checking if an association record exists or not.

When you add the column you have to check if the row exists and the value it stores, either making the lookup slower or increasing the size of the index on your association table.

Beware of premature optimization.