0
\$\begingroup\$

I want to have a like system on my website on multiple pages like "news", "comments", "forum posts" etc. So I was wondering what was the best way to do it between:

  • having like tables for each of my "modules" (news, comments...)
  • having one table like :

    table: likes
    id (int) | page_id (varchar) | user_id (int)

which would have those datas:

5 | news_5576    | 676
6 | comment_6564 | 676
7 | forum_656    | 787

And I would get the number of likes of a module with his id and type:

$type = 'comment';
$id = 6564;
$query = "SELECT COUNT(*) FROM likes WHERE page_id = '$type_$id'";

What do you think is the best idea?

\$\endgroup\$

closed as primarily opinion-based by rolfl, Yuushi, Marc-Andre, Mathieu Guindon, Jeff Vanzella Mar 3 '14 at 3:30

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

2
\$\begingroup\$

This design violates first normal form, which is a concept that states that each data field should contain an atomic value (using type and ID together makes this a composite value).

This makes certain types of queries more difficult, and you'll use more database space and suffer query performance.

Store the type in one column, and the ID in another. If you have many tables, you might consider breaking it out so each module type has a corresponding "likes" table (e.g. news and news_likes. This uses significantly less space and makes maintenance easier, but makes some queries harder (such as "show me all my likes").

Using a single-table notation is acceptable, but by breaking the type and ID out, you can use an enum and an int instead of a var char, which will improve query speed on full-table scans, and use less space on disk.

\$\endgroup\$
  • \$\begingroup\$ I was also thinking about the last solution and for the reason you quoted: "show me all my likes". But yes, it would take more space. In fact, I'm wondering this because the last developer of the website used "comment_id" for another module (comment system). \$\endgroup\$ – Codel96 Mar 3 '14 at 0:22
  • \$\begingroup\$ In my blog, which isn't yet online, I used the latter method; one table with an enum and id column. It works a treat, and queries are simple and easy to expand on. \$\endgroup\$ – phyrfox Mar 3 '14 at 0:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.