好友游戏匹配APP的问卷数据库结构及功能实现技术问询

Hey there! Let's work through a solid, scalable database structure for your 2-player game picker app. Since it's built specifically for two friends, we can keep it focused but flexible enough to handle all the core features you mentioned—storing user names, capturing survey responses, and powering that random game picker button.

Core Table Structure #

Here are the key tables you'll need, with SQL definitions to make implementation straightforward:

1. Users Table #

Stores basic info for each friend. Even though you're targeting two users, this structure lets you easily add more pairs later if you want to expand.

CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL, -- e.g., "Alex" or "Sam"
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP -- Tracks when the user was added
);

2. Games Table #

Holds all the candidate games your friends might want to play. This makes it easy to update the game list without touching survey logic.

CREATE TABLE Games (
    game_id INT PRIMARY KEY AUTO_INCREMENT,
    game_name VARCHAR(100) NOT NULL, -- e.g., "Stardew Valley" or "Mario Kart 8"
    description TEXT NULL -- Optional: Add a quick blurb about the game
);

3. Survey Responses Table #

This is the heart of your matching system—it captures each user's preferences for every game, including that key "willing to let my friend play this" question.

CREATE TABLE SurveyResponses (
    response_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    game_id INT NOT NULL,
    willing_to_play BOOLEAN NOT NULL, -- 1 = Yes, 0 = No (wants to play the game)
    allow_friend_play BOOLEAN NOT NULL, -- 1 = Yes, 0 = No (okay with friend playing this content)
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (game_id) REFERENCES Games(game_id),
    UNIQUE KEY unique_user_game (user_id, game_id) -- Prevents duplicate responses for the same user/game
);

4. Friend Pairs Table #

Binds the two friends together so your app can quickly look up their shared preferences. This ensures you're only matching the specific pair you care about.

CREATE TABLE FriendPairs (
    pair_id INT PRIMARY KEY AUTO_INCREMENT,
    user1_id INT NOT NULL,
    user2_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user1_id) REFERENCES Users(user_id),
    FOREIGN KEY (user2_id) REFERENCES Users(user_id),
    UNIQUE KEY unique_pair (user1_id, user2_id) -- Stops duplicate pairs from being created
);

How the Matching Logic Works #

When your friends hit that "Pick a Game" button, here's what the app will do behind the scenes:

• Use the FriendPairs table to find the current user's linked friend
• Query the SurveyResponses table to find games where both users have willing_to_play = 1 AND allow_friend_play = 1
• Randomly select one game from that matching set
• Pull the friend's name from the Users table and display your desired message: 今天在‘[好友姓名]’家玩[游戏名称]

Quick Optimization Tips #

• If you ever want to move beyond pure random picks, add a preference_score INT (1-5) to the SurveyResponses table. You can use this to weight picks toward games both friends love more.
• Add a genre VARCHAR(50) column to the Games table (e.g., "Co-op RPG" or "Party Game") if you want to let your friends filter picks by category later.

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