Database Design – Designing for ‘Not Seen’ Queries in MySQL and MongoDB

database-designmongodbMySQL

I'm building a large collection of items from which I want to query items that a specific user has not seen before. I will need to track, per user, which items s/he has seen.

Currently the items are stored in mongodb and the 'seen items' I am planning to store in a mysql database.

I have two questions:

  1. How should I design the database of items in order to effectively query for items a certain user has not seen?
  2. As each user can have 'seen' several thousand items, should each user have its own mysql table or should I just have one large table?

EDIT: The items the user get are from the collection of items the user has not seen are selected randomly

Best Answer

The data says that you need

CREATE TABLE Seen (
    user_id ...,
    item_id ...,
    PRIMARY KEY(user_id, item_id),
    INDEX      (item_id, user_id)
) ENGINE=InnoDB;

That is in addition to table Users, with PRIMARY KEY (user_id) and Items with PRIMARY KEY (item_id).

Items not seen by user 1234:

SELECT i.item_id
    FROM Items AS i
    LEFT JOIN Seen AS s  ON  s.item_id = i.item_id
                         AND s.user_id = 1234;
    WHERE s.item_id IS NULL ;

You could then JOIN to Users and/or Items to get more info.

Separate tables for separate users -- NO. This is a common question with the same answer every time.

Related Question