How to arrange MySQL database to track users that have associated to multiple primary keys?

Question

I have a table in MySQL with multiple of reported events (primary key, date submitted, and text). Users can "like", or "+1" the reports, similar to facebook/google. Right now, a user can "like" a single event infinite times. What's the best way of arranging the database to check if he's agreed to this even before?

I considered adding a column that can store a huge amount of text and adding a user to this text block if he agrees (comma seperated). I could then explode the text later and search it to see if his name is in there. However, I figured there must be a better way of going about this - any suggestions?

score 2 · Accepted Answer · edited May 23 '17 at 10:33

I'd use three tables: event, user and user_likes_event

CREATE TABLE `event` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `submitted` DATE NULL ,
  `text` TEXT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

CREATE TABLE `user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

CREATE TABLE `user_likes_event` (
  `event_id` INT UNSIGNED NOT NULL ,
  `user_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`event_id`, `user_id`) ,
  INDEX `fk_user_id` (`user_id` ASC) ,
  INDEX `fk_event_id` (`event_id` ASC) ,
  CONSTRAINT `fk_event_id`
    FOREIGN KEY (`event_id` )
    REFERENCES `event` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_user_id`
    FOREIGN KEY (`user_id` )
    REFERENCES `user` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Insert some values:

INSERT INTO `event` (submitted, text) VALUES
('2011-06-20', 'Event 1'),
('2011-06-21', 'Event 2'),
('2011-06-22', 'Event 3');

INSERT INTO `user` (name) VALUES
('Tom'), ('Dick'), ('Harry');

INSERT INTO `user_likes_event` (event_id, user_id) VALUES
(1,1), (1,2), (1,3),
(2,1), (2,2), (2,3),
(3,1), (3,2), (3,3);

Every user now likes every event once. If Tom attempts to like Event 1 again, a primary key constraint error is thrown:

INSERT INTO `user_likes_event` (event_id, user_id) VALUES (1,1);

ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY'

If you don't want errors thrown, you could try a conditional insert:

INSERT INTO `user_likes_event` (event_id, user_id)
SELECT 1,1 FROM DUAL
WHERE NOT EXISTS (
    SELECT event_id, user_id   FROM `user_likes_event`
    WHERE event_id = 1 AND user_id = 1
);

See this question for ideas on INSERT WHERE NOT EXISTS alternatives.

See the SELECT Syntax for more information on the DUAL table.

score 1 · Answer 2 · answered Jun 22 '11 at 06:55

Why wouldn't you have a table like:

UserId INT NOT NULL
Submitted DATE NOT NULL
ReportId INT NOT NULL
PRIMARY KEY (UserId, ReportId)
FOREIGN KEY UserId REFERNCES Users (Id)
FOREIGN KEY ReportId REFERENCES Reports(Id)

That will prohibit "infinite" likes/+1s and you won't have to parse and search large strings. It may not be too efficient once you get lots of millions of "likes" but it should be good for most situations. It depends on how many "likes" you think will be generated and inserted into your database.

How to arrange MySQL database to track users that have associated to multiple primary keys?

2 Answers2