How should I design a re-usable content like table?

Question

So I've got a database that holds articles, recipes, and comments in seperate tables, and I'm wanting to allow users to be able to like each of these content pieces (articles, recipes, comments).

I'm wondering whether it's a good idea to create a re-usable likes table which would look something like this:

| content_type | user_id | content_id |
| article      | 1       | 1          |
| article      | 1       | 3          |
| article      | 1       | 4          |
| comment      | 2       | 1          |
| comment      | 3       | 1          |
| recipe       | 2       | 1          |

So you'd have:

• content_type:

which would represent either article, recipe, or comment

• user_id:

which represents the user who liked the content

• content_id:

this would be the primary key of the content piece (article, comment, recipe).

Is it a good idea to do this? There is the potential for millions of records to be inserted into the one table.

The alternative (which I am using right now) is that I have a table for each of the content types (articles, comment, recipe).

The thing is, I might be adding more content types to my database, which means the likes schema I proposed might be a better idea.

Can anyone point out any cons of using a schema like this?

Answer 1

I think this is probably a good design that you proposed. Just make sure that the table is indexed properly. Consider that every query to the table will probably filter by content_type so that should probably be the leading column in a multi-field index.