大家做多级代理项目的时候怎么设计sql表？

Question

要不要在每个表里面都加一个parentId字段，这样可以根据这个字段查到所有下级对应表的情况。
但是感觉每个表都多一个字段比较麻烦，还有别的方案吗？

Answer 1

1.用户表

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
);

2.代理表

CREATE TABLE proxies (
    proxy_id INT PRIMARY KEY,
    ip_address VARCHAR(50) NOT NULL,
    port INT NOT NULL,
    username VARCHAR(50),
    password VARCHAR(50),
    status ENUM('available', 'unavailable', 'expired') NOT NULL
);

3.代理关系表

CREATE TABLE proxy_users (
    user_id INT,
    proxy_id INT,
    PRIMARY KEY (user_id, proxy_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (proxy_id) REFERENCES proxies(proxy_id)
);

4.流量表

CREATE TABLE traffic (
    traffic_id INT PRIMARY KEY,
    user_id INT,
    proxy_id INT,
    traffic_type ENUM('http', 'https', 'ftp', 'telnet') NOT NULL,
    traffic_size INT NOT NULL,
    traffic_time TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (proxy_id) REFERENCES proxies(proxy_id)
);

5.日志表

CREATE TABLE access_logs (
    log_id INT PRIMARY KEY,
    user_id INT,
    proxy_id INT,
    access_time TIMESTAMP NOT NULL,
    access_url VARCHAR(255) NOT NULL,
    access_result ENUM('success', 'failure') NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (proxy_id) REFERENCES proxies(proxy_id)
);

最后要根据你项目的具体需求进行调整