Skip to content

EBD

Jump to bottom
Simão Barbosa edited this page Feb 27, 2026 · 1 revision

EBD: Database Specification Component

PingPong Q&A helps communities share and build knowledge in a simple, organized way. By making it easy to ask questions, find answers, and recognize contributions, it encourages people to engage and learn together. With smart search, badges, and an interface that works on any device, the platform makes knowledge easier to discover, trust, and keep for the long term.

A4: Conceptual Data Model

This artifact defines the database structure by identifying all core entities and their relationships relevant to the system specification. This definition is formally documented using a UML class diagram, which includes detailed information on attributes, multiplicities, and logical constraints.

1. Class diagram

Class Diagram{width=900 height=599}

2. Additional Business Rules

  • BR110. A Member must have a reputation score above a defined threshold (e.g., 50) to cast a downvote.
  • BR111. An Author is prohibited from casting a vote (upvote or downvote) on their own Question or Answer.
  • BR112. If a Moderator or Administrator deletes content due to a violation, the Author will incur an immediate, significant reputation penalty.

A5: Relational Schema, validation and schema refinement

This artifact presents the Relational Schema derived from the conceptual data model. It includes the relation schemas in compact notation, domain definitions, and a validation of the schema's normalization to ensure it meets the Boyce-Codd Normal Form (BCNF).

1. Relational Schema

This section details the structure of all database tables, their attributes, and the key constraints (PK, FK, UK).

Relation reference Relation Compact Notation
R01 users(id, username UK NN, email UK NN, password NN,google_id, google_id, display_name, bio, avatar_url, reputation NN DF 0 CK reputation >= 0, role NN DF 'Member' CK role IN UserRole), is_blocked NN DF FALSE, remember_token, notification_preferences
R02 badge(id, name UK NN, description NN)
R03 user_badge(user_id -> users, badge_id -> badge, awarded_at NN DF Now)
R04 notification(id, user_id -> users NN, content NN, type NN CK type IN NotificationType, is_read NN DF FALSE, snoozed_until, link, created_at NN DF Now CK created_at <= CURRENT_TIMESTAMP)
R05 tag(id, name UK NN)
R06 question(id, author_id -> users NN, accepted_answer_id -> answer UK, title NN, body NN, search_vector,created_at NN DF Now CK created_at <= CURRENT_TIMESTAMP, edited_at NN DF Now CK edited_at >= created_at)
R07 answer(id, author_id -> users NN, question_id -> question NN, body NN, search_vector, created_at NN DF Now CK created_at <= CURRENT_TIMESTAMP, edited_at NN DF Now CK edited_at >= created_at)
R08 comment(id, author_id -> users NN, question_id -> question, answer_id -> answer, body NN, created_at NN DF Now CK created_at <= CURRENT_TIMESTAMP, edited_at NN DF Now CK edited_at >= created_at, CK ((CASE WHEN question_id IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN answer_id IS NOT NULL THEN 1 ELSE 0 END) = 1))
R09 report(id, reporter_id -> users NN, reported_user_id -> users NN, question_id -> question, answer_id -> answer, comment_id -> comment, reason NN, status NN DF 'Pending' CK status IN ReportStatus, created_at NN DF Now, CK ((CASE WHEN question_id IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN answer_id IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN comment_id IS NOT NULL THEN 1 ELSE 0 END) = 1))
R10 question_tag(question_id -> question, tag_id -> tag)
R11 question_follow(user_id -> users, question_id -> question)
R12 tag_follow(user_id -> users, tag_id -> tag)
R13 question_vote(user_id -> users, question_id -> question, is_positive NN)
R14 answer_vote(user_id -> users, answer_id -> answer, is_positive NN)
R15 tag_synonym(tag_id -> tag, name UK NN)
R16 attachment(id, question_id -> question NN, file_path NN)
R17 password_reset_tokens(email UK NN, token NN, created_at)
R18 warnings(id, user_id -> users NN, moderator_id -> users NN, reason NN, created_at NN DF Now, updated_at NN DF Now)
R19 suspensions(id, user_id -> users NN , moderator_id -> users NN, reason NN, duration NN, created_at NN DF Now, updated_at NN DF Now)

Legend:

  • UK = UNIQUE KEY
  • NN = NOT NULL
  • DF = DEFAULT
  • CK = CHECK

2. Domains

This section specifies the custom data types, such as ENUMs and default value generators, used to enforce data consistency in the schema.

Domain Name Domain Specification
Now TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
UserRole ENUM ('Member', 'Moderator', 'Admin')
NotificationType ENUM ('Answer Posted', 'Vote Received', 'Badge Awarded', 'General', 'Answer Accepted')
ReportStatus ENUM ('Pending', 'In Review', 'Resolved', 'Rejected')

3. Schema validation

In this section we analyze the functional dependencies (FDs) of each relation to verify that the entire schema is properly normalized in Boyce-Codd Normal Form (BCNF).

TABLE R01 users
Keys {id}, {username}, {email}
Functional Dependencies:
FD0101 {id} → {username, email, password, google_id, github_id, display_name, bio, avatar_url, reputation, role , is_blocked, remember_token, notification_preferences}
FD0102 {username} → {id, email, password, google_id, github_id,display_name, bio, avatar_url, reputation, role, is_blocked, remember_token, notification_preferences}
FD0103 {email} → {id, username, password, google_id, github_id, display_name, bio, avatar_url, reputation, role, is_blocked, remember_token, notification_preferences}
NORMAL FORM BCNF (All determinants are superkeys)
TABLE R02 badge
Keys {id}, {name}
Functional Dependencies:
FD0201 {id} → {name, description}
FD0202 {name} → {id, description}
NORMAL FORM BCNF (All determinants are superkeys)
TABLE R03 user_badge
Keys {user_id, badge_id}
Functional Dependencies:
FD0301 {user_id, badge_id} → {awarded_at}
NORMAL FORM BCNF (The only determinant is a superkey)
TABLE R04 notification
Keys {id}
Functional Dependencies:
FD0401 {id} → {user_id, content, type, is_read, snoozed_until, link, created_at}
NORMAL FORM BCNF (The only determinant is a superkey)
TABLE R05 tag
Keys {id}, {name}
Functional Dependencies:
FD0501 {id} → {name}
FD0502 {name} → {id}
NORMAL FORM BCNF (All determinants are superkeys)
TABLE R06 question
Keys {id}, {accepted_answer_id} (if NOT NULL)
Functional Dependencies:
FD0601 {id} → {author_id, accepted_answer_id, title, body, search_vector, created_at, edited_at}
FD0602 {accepted_answer_id} → {id, author_id, title, body, search_vector, created_at, edited_at}
NORMAL FORM BCNF (All determinants are superkeys)
TABLE R07 answer
Keys {id}
Functional Dependencies:
FD0701 {id} → {author_id, question_id, body, search_vector, created_at, edited_at}
NORMAL FORM BCNF (The only determinant is a superkey)
TABLE R08 comment
Keys {id}
Functional Dependencies:
FD0801 {id} → {author_id, question_id, answer_id, body, created_at, edited_at}
NORMAL FORM BCNF (The only determinant is a superkey)
TABLE R09 report
Keys {id}
Functional Dependencies:
FD0901 {id} → {reporter_id, reported_user_id, question_id, answer_id, comment_id, reason, status, created_at}
NORMAL FORM BCNF (The only determinant is a superkey)
TABLE R10 question_tag
Keys {question_id, tag_id}
Functional Dependencies: None (trivial)
NORMAL FORM BCNF (No non-trivial FDs)
TABLE R11 question_follow
Keys {user_id, question_id}
Functional Dependencies: None (trivial)
NORMAL FORM BCNF (No non-trivial FDs)
TABLE R12 tag_follow
Keys {user_id, tag_id}
Functional Dependencies: None (trivial)
NORMAL FORM BCNF (No non-trivial FDs)
TABLE R13 question_vote
Keys {user_id, question_id}
Functional Dependencies:
FD1301 {user_id, question_id} → {is_positive}
NORMAL FORM BCNF (The only determinant is a superkey)
TABLE R14 answer_vote
Keys {user_id, answer_id}
Functional Dependencies:
FD1401 {user_id, answer_id} → {is_positive}
NORMAL FORM BCNF (The only determinant is a superkey)
TABLE R15 tag_synonym
Keys {tag_id, name}, {name}
Functional Dependencies:
FD1501 {tag_id, name} → {} (Trivial)
FD1502 {name} → {tag_id}
NORMAL FORM BCNF (All determinants are superkeys)
TABLE R16 attachment
Keys {id}
Functional Dependencies:
FD1601 {id} → {question_id, file_path}
NORMAL FORM BCNF (The only determinant is a superkey)
TABLE R17 password_reset_tokens
Keys {email}
Functional Dependencies:
FD1701 {email} → {token, created_at}
NORMAL FORM BCNF (The only determinant is a superkey)
TABLE R18 warnings
Keys {id}
Functional Dependencies:
FD1801 {id} → {user_id, moderator_id, reason, created_at, updated_at}
NORMAL FORM BCNF (The only determinant is a superkey)
TABLE R19 suspensions
Keys {id}
Functional Dependencies:
FD1901 {id} → {user_id, moderator_id, reason, duration, created_at, updated_at}
NORMAL FORM BCNF (The only determinant is a superkey)

Justification:

All relation schemas in this schema are in Boyce-Codd Normal Form (BCNF). This is because for every non-trivial functional dependency (FD) X -> Y found in any relation, the determinant X is a superkey for that relation.

Since all relations are in BCNF, the schema is inherently free of redundancy caused by functional dependencies, and no further normalization is required.

A6: Indexes, triggers, transactions and database population

This artifact specifies the database's dynamic components and optimization strategies. It analyzes the expected data workload, proposes indexes for performance and full-text search, defines triggers to enforce complex business rules, and outlines critical transactions to ensure data integrity.

1. Database Workload

This section provides a study of the predicted system load and data growth. This analysis is crucial for identifying which relations will become performance bottlenecks and require optimization via indexing.

Relation reference Relation Name Order of magnitude Estimated growth
R01 users thousands dozens per day
R02 badge dozens units per year
R03 user_badge thousands dozens per day
R04 notification tens of thousands hundreds per day
R05 tag hundreds units per month
R06 question thousands dozens per day
R07 answer tens of thousands hundreds per day
R08 comment tens of thousands hundreds per day
R09 report hundreds units per day
R10 question_tag thousands dozens per day
R11 question_follow tens of thousands hundreds per day
R12 tag_follow thousands dozens per day
R13 question_vote tens of thousands hundreds per day
R14 answer_vote hundreds of thousands thousands per day
R15 tag_synonym hundreds units per month
R16 attachment thousands dozens per day
R17 password_reset_tokens hundreds per day varies
R18 warnings thousands hundreds per day
R19 suspensions thousands dozens per day

2. Proposed Indices

2.1. Performance Indices

The following are the three B-tree indices predicted to have the biggest impact on application performance. These are proposed for high-frequency queries on large relations. Indices are not proposed for PK or UK columns, as PostgreSQL creates those automatically.

Index IDX01
Relation answer (R07)
Attribute question_id
Type B-tree
Cardinality High
Clustering High (Answers for the same question are written close together in time)
Justification Critical for US110 ("View Question Details"). This index allows rapidly fetching all answers for a given question. answer will be one of the largest tables, and this is the most frequent query.
SQL code CREATE INDEX ON "answer" ("question_id");
Index IDX02
Relation question_tag (R10)
Attribute tag_id
Type B-tree
Cardinality Medium-High
Clustering Medium
Justification Critical for US19 ("Browse Questions by Tags"). The default PK is not efficient for finding all questions for one tag. This index is essential for the main navigation/discovery feature.
SQL code CREATE INDEX ON "question_tag" ("tag_id");
Index IDX03
Relation answer (R07)
Attribute author_id
Type B-tree
Cardinality Medium-High (Many users, many answers)
Clustering Low
Justification Supports US27 ("View My Answers") and US111 ("View Public User Profiles"). This query will be very frequent for loading profile pages and runs against the large answer table.
SQL code CREATE INDEX ON "answer" ("author_id");

2.2. Full-text Search Indices

To support US12 ("Full-text Search"), we will implement full-text search over question (title and body) and answer (body). This requires adding tsvector columns, using GIN indexes for performance, and triggers to keep the vectors updated.

Index IDX_FTS_01
Relation question (R06)
Attribute search_vector (new tsvector column)
Type GIN
Clustering N/A
Justification Provides FTS on question titles and bodies. Titles are weighted higher (A) than bodies (B) to prioritize matches in the title. A GIN index is the standard for fast tsvector lookups.
SQL code ALTER TABLE "question" ADD COLUMN "search_vector" TSVECTOR;
CREATE INDEX ON "question" USING GIN ("search_vector");
UPDATE "question" SET "search_vector" =
setweight(to_tsvector('english', "title"), 'A')
setweight(to_tsvector('english', "body"), 'B');
Index IDX_FTS_02
Relation answer (R07)
Attribute search_vector (new tsvector column)
Type GIN
Clustering N/A
Justification Provides FTS on answer bodies (Weight C). This allows search results to include matches from high-quality answers, not just questions.
SQL code ALTER TABLE "answer" ADD COLUMN "search_vector" TSVECTOR;
CREATE INDEX ON "answer" USING GIN ("search_vector");
UPDATE "answer" SET "search_vector" = to_tsvector('english', "body");

3. Triggers

Triggers are defined to enforce complex business rules that cannot be handled by simple CHECK constraints, such as the new rules from A4 (BR110, BR111) and the FTS index maintenance.

Trigger TRG01: prevent_question_self_vote
Description Implements BR111 ("An Author is prohibited from casting a vote on their own Question"). This function runs before an insert/update on question_vote and blocks the action if the user_id matches the question.author_id.
SQL code CREATE OR REPLACE FUNCTION check_question_self_vote()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.user_id = (SELECT author_id FROM question WHERE id = NEW.question_id) THEN
RAISE EXCEPTION 'Users cannot vote on their own questions.';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_question_self_vote
BEFORE INSERT OR UPDATE ON question_vote
FOR EACH ROW EXECUTE FUNCTION check_question_self_vote();
Trigger TRG02: prevent_answer_self_vote
Description Implements BR111 ("An Author is prohibited from casting a vote on their own Answer"). This function runs before an insert/update on answer_vote and blocks the action if the user_id matches the answer.author_id.
SQL code CREATE OR REPLACE FUNCTION check_answer_self_vote()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.user_id = (SELECT author_id FROM answer WHERE id = NEW.answer_id) THEN
RAISE EXCEPTION 'Users cannot vote on their own answers.';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_answer_self_vote
BEFORE INSERT OR UPDATE ON answer_vote
FOR EACH ROW EXECUTE FUNCTION check_answer_self_vote();
Trigger TRG03: check_downvote_reputation
Description Implements BR110 ("A Member must have a reputation score above a defined threshold (e.g., 50) to cast a downvote"). This function checks the user's reputation only if is_positive = FALSE and blocks the action if reputation is not sufficient.
SQL code CREATE OR REPLACE FUNCTION check_downvote_reputation()
RETURNS TRIGGER AS $$
DECLARE user_rep INTEGER;
BEGIN
IF NEW.is_positive = FALSE THEN
SELECT reputation INTO user_rep FROM users WHERE id = NEW.user_id;
IF user_rep <= 50 THEN
RAISE EXCEPTION 'Users must have more than 50 reputation to downvote.';
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_question_downvote
BEFORE INSERT OR UPDATE ON question_vote
FOR EACH ROW EXECUTE FUNCTION check_downvote_reputation();

CREATE TRIGGER check_answer_downvote
BEFORE INSERT OR UPDATE ON answer_vote
FOR EACH ROW EXECUTE FUNCTION check_downvote_reputation();
Trigger TRG04: ts_question_update
Description Maintains the FTS index IDX_FTS_01. This trigger function automatically updates the search_vector column whenever a question's title or body is inserted or updated, ensuring the search index is always current.
SQL code CREATE OR REPLACE FUNCTION update_question_search_vector()
RETURNS TRIGGER AS $$
BEGIN
NEW.search_vector = setweight(to_tsvector('english', NEW.title), 'A')
setweight(to_tsvector('english', NEW.body), 'B');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ts_question_update
BEFORE INSERT OR UPDATE ON question
FOR EACH ROW EXECUTE FUNCTION update_question_search_vector();
Trigger TRG05: ts_answer_update
Description Maintains the FTS index IDX_FTS_02. This trigger function automatically updates the search_vector column whenever an answer's body is inserted or updated.
SQL code CREATE OR REPLACE FUNCTION update_answer_search_vector()
RETURNS TRIGGER AS $$
BEGIN
NEW.search_vector = to_tsvector('english', NEW.body);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ts_answer_update
BEFORE INSERT OR UPDATE ON answer
FOR EACH ROW EXECUTE FUNCTION update_answer_search_vector();

4. Transactions

The following transactions are defined to ensure atomicity for multi-step operations critical to the application's logic and data integrity.

SQL Reference T01: Post New Question (US22)
Justification Implements US22. Ensures that when a user posts a question, the question (R06), its tags (R10), and any attachments (R16) are saved atomically. This prevents orphaned questions or partial data, upholding BR.107 (must have at least one tag).
Isolation level READ COMMITTED (Default). This is sufficient as the transaction only inserts new data and does not depend on complex, concurrent reads that would be subject to phantoms.
Complete SQL Code BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Application logic provides: $author_id, $title, $body
INSERT INTO "question" ("author_id", "title", "body")
VALUES ($author_id, $title, $body) RETURNING "id";

-- Application logic provides: $new_question_id (from RETURNING), $tag_id_1, $tag_id_2, ...
INSERT INTO "question_tag" ("question_id", "tag_id")
VALUES ($new_question_id, $tag_id_1),
($new_question_id, $tag_id_2);

-- Application logic provides: $file_path_1, $file_path_2, ...
INSERT INTO "attachment" ("question_id", "file_path")
VALUES ($new_question_id, $file_path_1),
($new_question_id, $file_path_2);

COMMIT;
SQL Reference T02: Vote on Answer (US29 & BR.104)
Justification Implements US29 and BR.104. Atomically records a vote (R14) and updates the answer author's reputation (R01). This prevents race conditions and ensures the vote and reputation change are never out of sync.
Isolation level READ COMMITTED. This is sufficient. While it reads reputation, it performs a relative update (reputation = reputation + X), which is generally safe from lost updates if handled in one transaction.
Complete SQL Code BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Application logic provides: $voter_id, $answer_id, $is_positive, $author_id, $rep_change
INSERT INTO "answer_vote" ("user_id", "answer_id", "is_positive")
VALUES ($voter_id, $answer_id, $is_positive)
ON CONFLICT ("user_id", "answer_id") DO UPDATE
SET "is_positive" = EXCLUDED.is_positive;

-- The $rep_change value is calculated by the application (e.g., +10 for upvote, -2 for downvote)
UPDATE "users" SET "reputation" = "reputation" + $rep_change
WHERE "id" = $author_id;

COMMIT;
SQL Reference T03: Accept Answer (US38)
Justification Implements US38. Atomically sets the accepted_answer_id on the question (R06) and updates the reputation for both the answer's author (R01) and the question's author (R01) as a reward.
Isolation level READ COMMITTED. The operations are straightforward updates based on primary keys and are not subject to complex read phenomena.
Complete SQL Code BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Application logic provides: $question_id, $answer_id, $answer_author_id, $question_author_id
UPDATE "question" SET "accepted_answer_id" = $answer_id
WHERE "id" = $question_id;

-- Reward answer author (e.g., +15 reputation)
UPDATE "users" SET "reputation" = "reputation" + 15
WHERE "id" = $answer_author_id;

-- Reward question author for accepting (e.g., +2 reputation)
UPDATE "users" SET "reputation" = "reputation" + 2
WHERE "id" = $question_author_id;

COMMIT;
SQL Reference T04: Anonymize User Account (US217, US53, BR.011)
Justification Implements the account deletion and anonymization policy. A single DELETE on the users table (R01) initiates a cascade of actions: ON DELETE CASCADE removes all personal data (votes, follows, badges, etc.), and ON DELETE SET NULL anonymizes all public content (questions, answers, comments). The user's PII (bio, display_name, etc.) is deleted with the user row.
Isolation level SERIALIZABLE. This is a highly destructive, critical operation. SERIALIZABLE ensures that no other transactions (e.g., a new post from this user, a new vote on their content) can interfere during the deletion, preventing anomalies.
Complete SQL Code BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Application logic provides: $user_id_to_delete
DELETE FROM "users" WHERE "id" = $user_id_to_delete;

COMMIT;
SQL Reference T05: Create Tag with Synonyms (US51)
Justification Implements US51. Ensures that a new tag (R05) and all its associated synonyms (R15) are created atomically. This prevents orphaned synonyms or tags.
Isolation level READ COMMITTED. This is sufficient as the transaction only inserts new data.
Complete SQL Code BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Application logic provides: $tag_name
INSERT INTO "tag" ("name") VALUES ($tag_name) RETURNING "id";

-- Application logic provides: $new_tag_id (from RETURNING), $synonym_1, $synonym_2, ...
INSERT INTO "tag_synonym" ("tag_id", "name")
VALUES ($new_tag_id, $synonym_1),
($new_tag_id, $synonym_2);

COMMIT;

Annex A. SQL Code

This annex contains the complete SQL scripts for database creation and population.

A.1. Database schema

The following SQL script defines the entire database structure, including all tables, types, and constraints.

-----------------------------------------
-- Drop existing schema
-----------------------------------------
DROP TABLE IF EXISTS "password_reset_tokens", "answer_vote", "question_vote", "tag_follow", "question_follow", "question_tag", "report", "comment", "attachment", "answer", "question", "notification", "user_badge", "tag_synonym", "tag", "badge", "users", "warnings", "suspensions" CASCADE;
DROP TYPE IF EXISTS "UserRole", "NotificationType", "ReportStatus";

-----------------------------------------
-- Domains (ENUM Types)
-----------------------------------------
CREATE TYPE "UserRole" AS ENUM ('Member', 'Moderator', 'Admin');
CREATE TYPE "NotificationType" AS ENUM ('Answer Posted', 'Vote Received', 'Badge Awarded', 'General','Answer Accepted');
CREATE TYPE "ReportStatus" AS ENUM ('Pending', 'In Review', 'Resolved', 'Rejected');

-----------------------------------------
-- Tables
-----------------------------------------

-- R01: users
CREATE TABLE "users" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "username" TEXT NOT NULL UNIQUE,
  "email" TEXT NOT NULL UNIQUE,
  "password" TEXT,
  "google_id" TEXT,
  "github_id" TEXT, 
  "display_name" TEXT,
  "bio" TEXT,
  "avatar_url" TEXT,
  "reputation" INTEGER NOT NULL DEFAULT 0 CHECK ("reputation" >= 0),
  "role" "UserRole" NOT NULL DEFAULT 'Member',
  "is_blocked" BOOLEAN NOT NULL DEFAULT FALSE,
  "remember_token" VARCHAR(100),
  "notification_preferences" JSON DEFAULT NULL
);

CREATE TABLE "password_reset_tokens" (
    "email" VARCHAR(255) PRIMARY KEY,
    "token" VARCHAR(255) NOT NULL,
    "created_at" TIMESTAMP(0) WITHOUT TIME ZONE
);

-- R02: badge
CREATE TABLE "badge" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "name" TEXT NOT NULL UNIQUE,
  "description" TEXT NOT NULL
);

-- R05: tag
CREATE TABLE "tag" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "name" TEXT NOT NULL UNIQUE
);

-- R06: question
CREATE TABLE "question" (

DROP TYPE IF EXISTS "UserRole", "NotificationType", "ReportStatus";

-----------------------------------------
-- Domains (ENUM Types)
-----------------------------------------
CREATE TYPE "UserRole" AS ENUM ('Member', 'Moderator', 'Admin');
CREATE TYPE "NotificationType" AS ENUM ('Answer Posted', 'Vote Received', 'Badge Awarded', 'General', 'Answer Accepted');
CREATE TYPE "ReportStatus" AS ENUM ('Pending', 'In Review', 'Resolved', 'Rejected');

-----------------------------------------
-- Tables
-----------------------------------------

-- R01: users
CREATE TABLE "users" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "username" TEXT NOT NULL UNIQUE,
  "email" TEXT NOT NULL UNIQUE,
  "password" TEXT,
  "google_id" TEXT,
  "github_id" TEXT, 
  "display_name" TEXT,
  "bio" TEXT,
  "avatar_url" TEXT,
  "reputation" INTEGER NOT NULL DEFAULT 0 CHECK ("reputation" >= 0),
  "role" "UserRole" NOT NULL DEFAULT 'Member',
  "is_blocked" BOOLEAN NOT NULL DEFAULT FALSE,
  "remember_token" VARCHAR(100),
  "notification_preferences" JSON DEFAULT NULL
);

-- R17: password_reset_tokens
CREATE TABLE "password_reset_tokens" (
    "email" VARCHAR(255) PRIMARY KEY,
    "token" VARCHAR(255) NOT NULL,
    "created_at" TIMESTAMP(0) WITHOUT TIME ZONE
);

-- R02: badge
CREATE TABLE "badge" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "name" TEXT NOT NULL UNIQUE,
  "description" TEXT NOT NULL
);

-- R05: tag
CREATE TABLE "tag" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "name" TEXT NOT NULL UNIQUE
);

-- R06: question
CREATE TABLE "question" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "author_id" INTEGER REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE,
  "accepted_answer_id" INTEGER UNIQUE, -- FK added after 'answer' table is created
  "title" TEXT NOT NULL,
  "body" TEXT NOT NULL,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "edited_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK ("created_at" <= CURRENT_TIMESTAMP),
  CHECK ("edited_at" >= "created_at")
);

-- R07: answer
CREATE TABLE "answer" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "author_id" INTEGER REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE,
  "question_id" INTEGER NOT NULL REFERENCES "question"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "body" TEXT NOT NULL,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "edited_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK ("edited_at" >= "created_at")
);

-- Add circular FK for R06
ALTER TABLE "question" ADD CONSTRAINT "fk_accepted_answer"
  FOREIGN KEY ("accepted_answer_id") REFERENCES "answer"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- R08: comment
CREATE TABLE "comment" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "author_id" INTEGER REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE,
  "question_id" INTEGER REFERENCES "question"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "answer_id" INTEGER REFERENCES "answer"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "body" TEXT NOT NULL,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "edited_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK ("edited_at" >= "created_at"),
  CHECK ((CASE WHEN "question_id" IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN "answer_id" IS NOT NULL THEN 1 ELSE 0 END) = 1)
);

-- R09: report
CREATE TABLE "report" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "reporter_id" INTEGER NOT NULL REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "reported_user_id" INTEGER REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE,
  "question_id" INTEGER REFERENCES "question"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "answer_id" INTEGER REFERENCES "answer"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "comment_id" INTEGER REFERENCES "comment"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "reason" TEXT NOT NULL,
  "status" "ReportStatus" NOT NULL DEFAULT 'Pending',
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK ((CASE WHEN "question_id" IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN "answer_id" IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN "comment_id" IS NOT NULL THEN 1 ELSE 0 END) = 1)
);

-- R04: notification
CREATE TABLE "notification" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "user_id" INTEGER NOT NULL REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "content" TEXT NOT NULL,
  "type" "NotificationType" NOT NULL,
  "is_read" BOOLEAN NOT NULL DEFAULT FALSE,
  "snoozed_until" TIMESTAMPTZ,
  "link" TEXT,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- R03: user_badge
CREATE TABLE "user_badge" (
  "user_id" INTEGER NOT NULL REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "badge_id" INTEGER NOT NULL REFERENCES "badge"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "awarded_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ("user_id", "badge_id")
);

-- R10: question_tag
CREATE TABLE "question_tag" (
  "question_id" INTEGER NOT NULL REFERENCES "question"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "tag_id" INTEGER NOT NULL REFERENCES "tag"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY ("question_id", "tag_id")
);

-- R11: question_follow
CREATE TABLE "question_follow" (
  "user_id" INTEGER NOT NULL REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "question_id" INTEGER NOT NULL REFERENCES "question"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY ("user_id", "question_id")
);

-- R12: tag_follow
CREATE TABLE "tag_follow" (
  "user_id" INTEGER NOT NULL REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "tag_id" INTEGER NOT NULL REFERENCES "tag"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY ("user_id", "tag_id")
);

-- R13: question_vote
CREATE TABLE "question_vote" (
  "user_id" INTEGER NOT NULL REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "question_id" INTEGER NOT NULL REFERENCES "question"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "is_positive" BOOLEAN NOT NULL,
  PRIMARY KEY ("user_id", "question_id")
);

-- R14: answer_vote
CREATE TABLE "answer_vote" (
  "user_id" INTEGER NOT NULL REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "answer_id" INTEGER NOT NULL REFERENCES "answer"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "is_positive" BOOLEAN NOT NULL,
  PRIMARY KEY ("user_id", "answer_id")
);

-- R15: tag_synonym
CREATE TABLE "tag_synonym" (
  "tag_id" INTEGER NOT NULL REFERENCES "tag"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "name" TEXT NOT NULL UNIQUE,
  PRIMARY KEY ("tag_id", "name")
);

-- R16: attachment
CREATE TABLE "attachment" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "question_id" INTEGER NOT NULL REFERENCES "question"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "file_path" TEXT NOT NULL
);

-- R18: warnings
CREATE TABLE warnings (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    moderator_id INTEGER NOT NULL,
    reason TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (moderator_id) REFERENCES users(id) ON DELETE CASCADE
);

-- R19: suspensions
CREATE TABLE suspensions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    moderator_id INTEGER NOT NULL,
    reason TEXT NOT NULL,
    duration INTEGER NOT NULL, -- Duration in days
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (moderator_id) REFERENCES users(id) ON DELETE CASCADE
);

-----------------------------------------
-- Performance Indexes (A6)
-----------------------------------------

-- IDX01: For US110 (View Question Details)
CREATE INDEX ON "answer" ("question_id");

-- IDX02: For US19 (Browse Questions by Tags)
CREATE INDEX ON "question_tag" ("tag_id");

-- IDX03: For US27/US111 (View My Answers / View Public User Profiles)
CREATE INDEX ON "answer" ("author_id");

-----------------------------------------
-- Full-Text Search Setup (A6)
-----------------------------------------

-- Add tsvector columns
ALTER TABLE "question" ADD COLUMN "search_vector" TSVECTOR;
ALTER TABLE "answer" ADD COLUMN "search_vector" TSVECTOR;

-- Create GIN indexes
CREATE INDEX ON "question" USING GIN ("search_vector");
CREATE INDEX ON "answer" USING GIN ("search_vector");

-- Populate vectors for existing data (run once after setup)
UPDATE "question" SET "search_vector" =
  setweight(to_tsvector('english', "title"), 'A') ||
  setweight(to_tsvector('english', "body"), 'B');

UPDATE "answer" SET "search_vector" = to_tsvector('english', "body");

-----------------------------------------
-- Triggers
-----------------------------------------

-- BR111: An Author is prohibited from casting a vote on their own Question.
CREATE OR REPLACE FUNCTION check_question_self_vote()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.user_id = (SELECT author_id FROM question WHERE id = NEW.question_id) THEN
    RAISE EXCEPTION 'Users cannot vote on their own questions.';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_question_self_vote
  BEFORE INSERT OR UPDATE ON question_vote
  FOR EACH ROW EXECUTE FUNCTION check_question_self_vote();

-- BR111: An Author is prohibited from casting a vote on their own Answer.
CREATE OR REPLACE FUNCTION check_answer_self_vote()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.user_id = (SELECT author_id FROM answer WHERE id = NEW.answer_id) THEN
    RAISE EXCEPTION 'Users cannot vote on their own answers.';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_answer_self_vote
  BEFORE INSERT OR UPDATE ON answer_vote
  FOR EACH ROW EXECUTE FUNCTION check_answer_self_vote();

-- BR110: A Member must have a reputation score above 50 to cast a downvote.
CREATE OR REPLACE FUNCTION check_downvote_reputation()
RETURNS TRIGGER AS $$
DECLARE
  user_rep INTEGER;
BEGIN
  -- Only check if it's a downvote (is_positive = FALSE)
  IF NEW.is_positive = FALSE THEN
    SELECT reputation INTO user_rep FROM users WHERE id = NEW.user_id;
    IF user_rep <= 50 THEN
      RAISE EXCEPTION 'Users must have more than 50 reputation to downvote.';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_question_downvote
  BEFORE INSERT OR UPDATE ON question_vote
  FOR EACH ROW EXECUTE FUNCTION check_downvote_reputation();

CREATE TRIGGER check_answer_downvote
  BEFORE INSERT OR UPDATE ON answer_vote
  FOR EACH ROW EXECUTE FUNCTION check_downvote_reputation();

-- TRG04: Maintain FTS vector for questions
CREATE OR REPLACE FUNCTION update_question_search_vector()
RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector = setweight(to_tsvector('english', NEW.title), 'A') ||
                      setweight(to_tsvector('english', NEW.body), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ts_question_update
  BEFORE INSERT OR UPDATE ON question
  FOR EACH ROW EXECUTE FUNCTION update_question_search_vector();

-- TRG05: Maintain FTS vector for answers
CREATE OR REPLACE FUNCTION update_answer_search_vector()
RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector = to_tsvector('english', NEW.body);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ts_answer_update
  BEFORE INSERT OR UPDATE ON answer
  FOR EACH ROW EXECUTE FUNCTION update_answer_search_vector();

-----------------------------------------
-- end
-----------------------------------------

A.2. Database population

The following SQL script populates the database with sample data for testing and demonstration purposes. The full SQL script can be found here.

-----------------------------------------
-- Population Script
-----------------------------------------

-- R01: users
-- Passwords are placeholders (e.g., 'password123' hashed)
INSERT INTO users (username, email, "password", display_name, bio, avatar_url, reputation, "role") VALUES
('admin_user', 'admin@pingpong.com', '$2y$10$Ifw.tZ1B.AxzP6.Jc.Y/B.JtEwYx/tEw.Jc.Y/B.JtEwYx/tEw', 
'Admin', 'Site Administrator', 'https://placehold.co/100x100/3F51B5/FFFFFF?text=A', 9999, 'Admin'),
('mod_user', 'mod@pingpong.com', '$2y$10$Ifw.tZ1B.AxzP6.Jc.Y/B.JtEwYx/tEw.Jc.Y/B.JtEwYx/tEw', 
'Moderator', 'Community Moderator. Keeping things clean.', 'https://placehold.co/100x100/FFC107/FFFFFF?text=M', 1000, 'Moderator'),
('simao', 'simao@example.com', '$2y$10$Ifw.tZ1B.AxzP6.Jc.Y/B.JtEwYx/tEw.Jc.Y/B.JtEwYx/tEw', 
'Simão Barbosa', 'Backend dev @ FEUP. PingPong enthusiast.', 'https://placehold.co/100x100/E8F5E9/4CAF50?text=S', 150, 'Member'),
('alex', 'alex@example.com', '$2y$10$Ifw.tZ1B.AxzP6.Jc.Y/B.JtEwYx/tEw.Jc.Y/B.JtEwYx/tEw', 
'Alex Ferreira', 'Frontend developer. Here for the JS questions.', 'https://placehold.co/100x100/E1F5FE/03A9F4?text=A', 40, 'Member'), -- Rep < 50, cannot downvote
('dinis', 'dinis@example.com', '$2y$10$Ifw.tZ1B.AxzP6.Jc.Y/B.JtEwYx/tEw.Jc.Y/B.JtEwYx/tEw', 
'Dinis Lima', 'Docker and CI/CD expert.', 'https://placehold.co/100x100/F3E5F5/9C27B0?text=D', 200, 'Member'),
('raul', 'raul@example.com', '$2y$10$Ifw.tZ1B.AxzP6.Jc.Y/B.JtEwYx/tEw.Jc.Y/B.JtEwYx/tEw', 
'Raul Oliveira', 'Just trying to learn SQL.', 'https://placehold.co/100x100/FFF3E0/FF9800?text=R', 5, 'Member'); -- Rep < 50, cannot downvote

-- removed for brevity

-----------------------------------------
-- end
-----------------------------------------

Revision history

Changes made to the first submission:

  1. 11/11/2025: Added bio, display_name and avatar attributes to users relation. Added a tag_synonym relation to support US51. Added a attachment relation to support US22. Updated the class diagram and SQL scripts to reflect changes.
  2. 22/12/2025: Added suspension and warning tables. Added notification_preferences attributes and is_blocked in user table. Added google_id and github_id to users for oAuth. Added snoozed_until and link attributes to notifications table. Added search_vector attribute to questions and answers table for search. Updated UML, Relational Schema and SQL script to reflect these changes.

GROUP2522, 03/11/2025

Clone this wiki locally