sql - how to add a reaction to a post (social website)

Question

I'm rather new to sql and i was wondering what would be the best way to add reactions to posts as we can see for exemple in twitter where you answer a tweet, so far i've worked out these 2 ways but i don't know which one should be done:

• add a reaction table which will have for pk id_react and id_post, with id_react in auto_increment with each reaction made by any user on any page of the site

this one is easier to put in place but i don't know if it's really viable for big scale web sites as it would result in a really big ass table and i was asking myself if it wouldn't make the search of those reactions slow in the long run

• create a new table reaction for every post that is created

this one would "erase" the problem of the DB size although it would mean to moderate as many reaction tables as there is of id_post; so here I am not knowing which solution would be theoretically the best for this kind of problem

标签： sqltheory

Answer 1

A new table per post does not look great to me. You may end up with millions of tables, most of them empty, for no performance benefit, and a lot of maintenance burden.

I would go with just one more table that stores all the reactions, and has post_id as a foreign key.

But your intuition about having more separate tables is not wrong: a similar feature is known as partitioning, and is available in all major relational databases.