如何设计支持用户关注度假项目的MySQL数据表

Hey there! Let's work through the right way to build your user-follow-vacation feature with MySQL, React, and Node.js. First, let's address the initial storage problem you're thinking through.

Why Avoid Storing Multiple IDs in a Single Field #

Storing a comma-separated list of vacation IDs in a followedVacations column on the users table (or user IDs in the vacations table) might seem simple at first, but it's a bad practice for relational databases like MySQL:

• Querying becomes messy: To check if a user follows a specific vacation, you'd have to do string operations instead of a clean join.
• Updating is error-prone: Adding/removing a vacation from a user's list requires parsing and modifying the string, which can lead to duplicates or invalid IDs.
• Scalability suffers: As your user base and vacation list grow, these string operations will get slower and harder to maintain.

The Correct Approach: A Join Table for Many-to-Many Relationships #

Since a user can follow multiple vacations, and a vacation can be followed by multiple users, this is a many-to-many relationship. The standard solution here is to create a dedicated join table to track these associations.

Example Table Schema #

Let's call it user_vacation_follows (feel free to pick a name that fits your project's naming convention):

CREATE TABLE user_vacation_follows (
    user_id INT NOT NULL,
    vacation_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, vacation_id), -- Prevents duplicate follows from the same user
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (vacation_id) REFERENCES vacations(id) ON DELETE CASCADE
);

• The composite primary key (user_id + vacation_id) ensures a user can't follow the same vacation more than once.
• ON DELETE CASCADE means if a user or vacation is deleted, all related follow records are automatically removed (keeps your data clean without extra code).

How to Use This Table #

• Follow a vacation: Insert a new row with the user's ID and the vacation's ID.
• Unfollow a vacation: Delete the row matching the user's ID and vacation's ID.
• Get a user's followed vacations: Join this table with the vacations table:

  SELECT v.* 
  FROM vacations v
  JOIN user_vacation_follows uvf ON v.id = uvf.vacation_id
  WHERE uvf.user_id = 123; -- Replace with the target user ID
  

• Get all followers of a vacation: Join with the users table:

  SELECT u.* 
  FROM users u
  JOIN user_vacation_follows uvf ON u.id = uvf.user_id
  WHERE uvf.vacation_id = 456; -- Replace with the target vacation ID
  

Should You Add a Follower Count Column to the vacations Table? #

Absolutely! But there are two approaches to consider, depending on your performance needs:

1. Denormalized Follower Count (For Frequent Queries) #

Add a follower_count column to the vacations table:

ALTER TABLE vacations ADD COLUMN follower_count INT DEFAULT 0;

• Pros: Querying the number of followers is instant (just select follower_count from vacations), which is perfect if you display this number everywhere on your site.
• Cons: You need to keep this count in sync with the user_vacation_follows table. You can do this two ways:
  • Database Triggers: Create triggers that increment/decrement follower_count whenever a row is inserted/deleted in user_vacation_follows.
  • Application Logic: In your Node.js backend, whenever a user follows/unfollows a vacation, update both the join table and the follower_count column in one transaction.

2. Calculated Follower Count (For Infrequent Queries) #

If you don't need to show the follower count constantly, you can calculate it on the fly using the join table:

SELECT COUNT(*) AS follower_count
FROM user_vacation_follows
WHERE vacation_id = 456;

• Pros: No need to maintain an extra column—data is always consistent.
• Cons: It's slightly slower than a direct column lookup, especially if a vacation has thousands of followers.

Final Recommendations #

• Start with the user_vacation_follows join table—it's the standard, scalable solution for many-to-many relationships.
• Add the follower_count column to vacations if you plan to display this number often (use triggers or transactional updates to keep it in sync).
• In your React frontend, fetch followed vacations and follower counts via Node.js API endpoints that run the SQL queries we outlined.

内容的提问来源于stack exchange，提问作者adame21