CODEWITHSUNDEEP
phpmysqldatabasecreate-table
Chris
Chris

Reputation: 435

mysql 'likes' structure table

I'm working on a web site where users can post articles with this table structure :

CREATE TABLE IF NOT EXISTS `articles` (
  `id_articles` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_users` int(10) unsigned NOT NULL,
  `articles` text NOT NULL,
  PRIMARY KEY (`id_articles`),
  UNIQUE KEY `id_articles` (`id_articles`),
  KEY `id_users` (`id_users`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Each user can 'like' the articles.

Is that the right way below to create a 'like table' : 

CREATE TABLE IF NOT EXISTS `articles_likes` (
  `id_articles` int(10) unsigned NOT NULL,
  `id_users` int(10) unsigned NOT NULL,
  KEY `id_articles` (`id_articles`,`id_users`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Upvotes: 2

Views: 1237

Answers (3)

user1467267
user1467267

Reputation:

This is a valid way Chris. You can use COUNT() to match the id_articles in the articles_likes table against the current article you are viewing in articles.

$articles_id = 23;

mysql_query("SELECT COUNT(*) FROM articles_likes
WHERE id_articles = ".$articles_id);

You can also just leave COUNT() (MySQL) out and instantly know which users are the "likers" of the articles and use count() (PHP) on the returned Array to duplicate the effect of COUNT() in MySQL.

Upvotes: 2

Tiberiu Savin
Tiberiu Savin

Reputation: 221

It is correct but you will want to add separte indexes on id_articles and id_users (also you might want to name the columns 'id_article' and 'id_user' for sanity).

CREATE TABLE IF NOT EXISTS `article_likes` (
    `id_article` int(10) unsigned NOT NULL,
    `id_user` int(10) unsigned NOT NULL,
     KEY `id_article` (`id_article`),
     KEY `id_user` (`id_user`)
) ENGINE=InnoDB;

The reason you want separate indexes is because in mysql if you create an index on columns (A, B) that index will be used in queries having in the where clause column A, or columns A and B. In your case for example if you made a query "SELECT * FROM article_likes WHERE id_user=X" this query would not use an index. An ever better option would be to add a combined index and a separate index on the second column from the combined index. Like this:

CREATE TABLE IF NOT EXISTS `article_likes` (
    `id_article` int(10) unsigned NOT NULL,
    `id_user` int(10) unsigned NOT NULL,
     KEY `id_article_user` (`id_article`, `id_user`),
     KEY `id_user` (`id_user`)
) ENGINE=InnoDB;

This way you would have optimal performance on queries like 'WHERE id_user=X', "WHERE id_article=X', "WHERE id_article=X AND id_user=Y"

Upvotes: 2

Jeff Hawthorne
Jeff Hawthorne

Reputation: 568

i would have a total of 3 tables. an articles table, and the user id could be a column in that for users who submit articles , but you need a separate user table since not all users will submit articles (i am assuming), and then a 3rd table for likes, that takes the primary key from users and the primary key from articles and uses them as foreign keys. so each time an article is liked, an entry is made in the 3rd table

Upvotes: 1

Related Questions