sql
用户表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
avatar_url TEXT,
bio TEXT,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
动态表
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id) ON DELETE CASCADE,
content TEXT,
media_urls JSONB, 存储图片/视频链接数组
likes_count INT DEFAULT 0,
comments_count INT DEFAULT 0,
created_at TIMESTAMP DEFAULT NOW()
);
点赞表
CREATE TABLE likes (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id) ON DELETE CASCADE,
post_id INT REFERENCES posts(id) ON DELETE CASCADE,
UNIQUE(user_id, post_id)
);
评论表
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id) ON DELETE CASCADE,
post_id INT REFERENCES posts(id) ON DELETE CASCADE,
content TEXT NOT NULL,
parent_comment_id INT REFERENCES comments(id), 支持嵌套评论
created_at TIMESTAMP DEFAULT NOW()
);
关注关系表
CREATE TABLE follows (
follower_id INT REFERENCES users(id) ON DELETE CASCADE,
followee_id INT REFERENCES users(id) ON DELETE CASCADE,
created_at TIMESTAMP DEFAULT NOW(),
PRIMARY KEY (follower_id, followee_id)
);