Changes between Version 3 and Version 4 of ddlScript.sql


Ignore:
Timestamp:
04/23/26 14:35:37 (9 days ago)
Author:
211099
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ddlScript.sql

    v3 v4  
    3030    username VARCHAR(255) NOT NULL UNIQUE,
    3131    email VARCHAR(255) NOT NULL UNIQUE,
    32     name VARCHAR(100) NOT NULL,
     32    user_name VARCHAR(100) NOT NULL,                       
    3333    surname VARCHAR(100) NOT NULL,
    3434    password VARCHAR(255) NOT NULL,
    35     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    36     updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     35    user_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 
     36    user_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    3737    CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
    3838);
    … …  
    6464    short_description VARCHAR(500) NOT NULL,
    6565    image VARCHAR(2048),
    66     content TEXT NOT NULL,
     66    story_content TEXT NOT NULL,                             
    6767    user_id INTEGER NOT NULL,
    68     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    69     updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     68    story_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     69    story_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    7070    FOREIGN KEY (user_id) REFERENCES WRITER(user_id)
    7171        ON DELETE CASCADE
    … …  
    8888    chapter_name VARCHAR(100) NOT NULL,
    8989    title VARCHAR(200) NOT NULL,
    90     content TEXT NOT NULL,
     90    chapter_content TEXT NOT NULL,                           
    9191    word_count INTEGER CHECK (word_count >= 0),
    9292    rating DECIMAL(3,2) CHECK (rating >= 0 AND rating <= 5),
    … …  
    9494    view_count INTEGER DEFAULT 0 CHECK (view_count >= 0),
    9595    story_id INTEGER NOT NULL,
    96     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    97     updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     96    chapter_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     97    chapter_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    9898    CONSTRAINT unique_chapter_number UNIQUE(story_id, chapter_number),
    9999    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
    … …  
    104104CREATE TABLE GENRE(
    105105    genre_id SERIAL PRIMARY KEY,
    106     name VARCHAR(100) NOT NULL UNIQUE
     106    genre_name VARCHAR(100) NOT NULL UNIQUE                 
    107107);
    108108
    109109CREATE TABLE READING_LIST(
    110110    list_id SERIAL PRIMARY KEY,
    111     name VARCHAR(100) NOT NULL,
    112     content TEXT,
     111    list_name VARCHAR(100) NOT NULL,                         
     112    list_content TEXT,                                     
    113113    is_public BOOLEAN NOT NULL,
    114114    user_id INTEGER NOT NULL,
    115     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    116     updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     115    list_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     116    list_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 
    117117    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    118118        ON DELETE CASCADE
    … …  
    122122CREATE TABLE NOTIFICATION(
    123123    notification_id SERIAL PRIMARY KEY,
    124     content TEXT NOT NULL,
     124    notification_content TEXT NOT NULL,                     
    125125    is_read BOOLEAN DEFAULT FALSE,
    126126    recipient_user_id INTEGER,
    127127    type VARCHAR(50),
    128128    link VARCHAR(500),
    129     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     129    notification_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    130130    FOREIGN KEY (recipient_user_id) REFERENCES USERS(user_id)
    131131        ON DELETE SET NULL
    … …  
    147147    suggested_text TEXT NOT NULL,
    148148    accepted BOOLEAN NOT NULL DEFAULT FALSE,
    149     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     149    suggestion_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    150150    applied_at TIMESTAMPTZ,
    151151    story_id INTEGER NOT NULL,
    … …  
    167167    user_id INTEGER,
    168168    story_id INTEGER,
    169     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     169    like_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    170170    CONSTRAINT like_pk PRIMARY KEY(user_id, story_id),
    171171    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    … …  
    179179CREATE TABLE COMMENT(
    180180    comment_id SERIAL PRIMARY KEY,
    181     content TEXT NOT NULL,
     181    comment_content TEXT NOT NULL,                         
    182182    user_id INTEGER NOT NULL,
    183183    story_id INTEGER NOT NULL,
    184     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    185     updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    186     FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    187         ON DELETE CASCADE
    188         ON UPDATE CASCADE,
    189     FOREIGN KEY (story_id) REFERENCES STORY(story_id)
    190         ON DELETE CASCADE
    191         ON UPDATE CASCADE
    192 );
     184    comment_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     185    comment_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     186    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     187        ON DELETE CASCADE
     188        ON UPDATE CASCADE,
     189    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     190        ON DELETE CASCADE
     191        ON UPDATE CASCADE
     192);
     193
    193194CREATE TABLE COLLABORATION(
    194195    user_id INTEGER,
    195196    story_id INTEGER,
    196     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     197    collab_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    197198    CONSTRAINT collaboration_pk PRIMARY KEY(user_id, story_id),
    198199    FOREIGN KEY (user_id) REFERENCES USERS(user_id)