news_site

CREATE DATABASE newsDB;
USE newsDB;

CREATE TABLE news_categories (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200)
);

CREATE TABLE news (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    content TEXT NOT NULL,
    publish_date DATE NOT NULL,

    category_id INT UNIQUE NOT NULL,
    CONSTRAINT FOREIGN KEY(category_id) REFERENCES news_categories(id)
);

CREATE TABLE images (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    url TEXT NOT NULL,

    news_id INT UNIQUE NOT NULL,
    CONSTRAINT FOREIGN KEY (news_id) REFERENCES news(id)
);

CREATE TABLE videos (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    url TEXT NOT NULL,

    news_id INT UNIQUE NOT NULL,
    CONSTRAINT FOREIGN KEY (news_id) REFERENCES news(id)
);

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(200) NOT NULL,
    role ENUM("Admin", "Editor", "Reader")
);

CREATE TABLE users_news (
    user_id INT UNIQUE NOT NULL,
    news_id INT UNIQUE NOT NULL,

    CONSTRAINT FOREIGN KEY(user_id) REFERENCES users(id),
    CONSTRAINT FOREIGN KEY(news_id) REFERENCES news(id),
    PRIMARY KEY(user_id, news_id)
);

CREATE TABLE comments (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    content TEXT NOT NULL,
    published_date DATE NOT NULL,

    news_id INT UNIQUE NOT NULL,
    user_id INT UNIQUE NOT NULL,

    CONSTRAINT FOREIGN KEY(news_id) REFERENCES news(id),
    CONSTRAINT FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE user_activity (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_action ENUM("Login", "Comment", "Delete account"),
    timestampp TIMESTAMP,

    user_id INT UNIQUE NOT NULL,
    CONSTRAINT FOREIGN KEY(user_id) REFERENCES users(id)
);