Relational Diagram - bounswe/bounswe2024group11 GitHub Wiki

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

CREATE TABLE Blocked_List(
    user_id VARCHAR(50),
    blocked_user_id VARCHAR(50) NOT NULL,
    ForeignKey(user_id) REFERENCES Registered_User(user_id),
    ForeignKey(blocked_user_id) REFERENCES Registered_User(user_id),
    PRIMARY KEY(user_id, blocked_user_id)
);

CREATE TABLE Muted_List(
    user_id VARCHAR(50),
    muted_user_id VARCHAR(50) NOT NULL,
    ForeignKey(user_id) REFERENCES Registered_User(user_id),
    ForeignKey(muted_user_id) REFERENCES Registered_User(user_id),
    PRIMARY KEY(user_id, muted_user_id)
);

CREATE TABLE Following_List(
    user_id VARCHAR(50),
    following_user_id VARCHAR(50) NOT NULL,
    ForeignKey(user_id) REFERENCES Registered_User(user_id),
    ForeignKey(following_user_id) REFERENCES Registered_User(user_id),
    PRIMARY KEY(user_id, following_user_id)
);

CREATE TABLE Follower_List(
    user_id VARCHAR(50),
    follower_user_id VARCHAR(50) NOT NULL,
    ForeignKey(user_id) REFERENCES Registered_User(user_id),
    ForeignKey(follower_user_id) REFERENCES Registered_User(user_id),
    PRIMARY KEY(user_id, follower_user_id)
);

CREATE TABLE Post(
    post_id INT AUTO_INCREMENT,
    author VARCHAR(50),
    text VARCHAR(50) NOT NULL,
    publication_date DATE NOT NULL,
    edit_date DATE NOT NULL,
    title VARCHAR(50) NOT NULL,
    picture VARCHAR(50) NOT NULL,
    comment_count INT NOT NULL,
    like_count INT NOT NULL,
    bookmark_count INT NOT NULL,
    PRIMARY KEY(post_id),
    ForeignKey(author) REFERENCES Registered_User(user_id)
);


CREATE TABLE Profile(
    user_id VARCHAR(50),
    biography VARCHAR(50) NOT NULL,
    profile_picture VARCHAR(50) NOT NULL,
    registration_date DATE NOT NULL,
    PRIMARY KEY(user_id),
    ForeignKey(user_id) REFERENCES Registered_User(user_id)
);