2

MySQL serves as our primary database, while MongoDB is employed for handling intensive write operations. I have been tasked with implementing a system for tracking visits and likes on products. The product data is stored in MySQL, but the visit and like data will be managed in MongoDB. To ensure uniqueness for likes and visits, we also need to monitor the users who have liked or visited the products. Therefore, I have devised the following schemas for implementation:

First Schema:

MySQL / Products Table
-----
product_id: 1,
...

product_id: 2,
...


MongoDB / Products Collection
---
product_id: 1
likes: [user_id_1, user_id_2, user_id_3... ]
visits: [user_id_1, user_id_2, user_id_3... ]

product_id: 2
likes: [user_id_1, user_id_2, user_id_3... ]
visits: [user_id_1, user_id_2, user_id_3... ]

Second Schema:

MySQL / Products Table
-----
product_id: 1,
...

product_id: 2,
...


MongoDB / Likes Collection
---
user_id: 1
product_id: 1

user_id: 2
product_id: 2

user_id: 3
product_id: 3

MongoDB / Visits Collection
---
user_id: 1
product_id: 1

user_id: 2
product_id: 2

user_id: 3
product_id: 3

I am uncertain whether these schemas align with MongoDB's best practices, so I am seeking guidance here. Are there any issues with these schemas?

3
  • Kind of an odd setup you have going on. If MySQL is your main database, and MongoDB is being used only for write intensive purposes (which arguably isn't any faster) then why aren't you managing the likes in your main database, since presumably you'll want to read this data?
    – J.D.
    Dec 16, 2023 at 12:56
  • @J.D. It was our DBA's idea, actually. He said that MySQL is not that good at writing operations but reading, so for write-intensive jobs like "counting likes" or "counting visits," he recommended using a NoSQL database. And our backend developers only had experience with MongoDB, so that's why we preferred this architecture.
    – BooRuleDie
    Dec 16, 2023 at 20:24
  • Gotcha. Yea he was wrong, all modern database systems have roughly the same capabilities for read and write performance. But if that was the tech stack most people were familiar with already then that would be a valid reason to utilize it. It's just interesting MySQL is your main database then.
    – J.D.
    Dec 16, 2023 at 22:19

2 Answers 2

0

Here is a possibility. The collections can be as follows:

products:

product_id: 1,
liked_by_users: [ "user_id_1", "user_id_3", ... ],
user_visits: [
  { id: "user_id_1", no_of: 2 }, 
  { id: "user_id_9", no_of: 14 }, 
  ... 
],
other info: { ... }

users:

id: "user_id_1",
name: "my name",
other info: { ... }

This allows accessing likes information as a product is accessed - e.g., total likes for the product. Then you can get how many users visited a product and total visits by all visitors for the product. You can also get info about how many times a user has visited a particular product. These queries require a "join" (a "lookup" aggregation) operation between products and users collections.

0

Go with just MySQL.

CREATE TABLE Liked (
    user_id ...,
    product_id ...,
    PRIMARY KEY(user_id, product_id),
    INDEX(product_id, user_id)
) ENGINE=InnoDB;

CREATE TABLE Visited (
    user_id ...,
    product_id ...,
    date DATE,
    PRIMARY KEY(user_id, product_id),
    INDEX(product_id, user_id),
    INDEX(date, ...) -- or other things
) ENGINE=InnoDB;

A PRIMARY KEY (in MySQL, at least) ensures uniqueness.

Simple up-votes might need a simpler table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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