Database Schema Documentation

The database schema design aims to create a robust structure for a blogging platform. It includes tables for users, posts, and comments, ensuring efficient organization and management of data. Additionally, SQL queries were crafted to retrieve posts by a specific user and count comments on each post, showcasing proficiency in data manipulation.

Schema `users` :

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Schema `posts` :

CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

Schema `comments` :

CREATE TABLE comments (
    comment_id SERIAL PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(post_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

Poupulating Tables:

INSERT INTO users (username, email, bio) VALUES
('john_doe', 'john@example.com', 'Software engineer and aspiring writer.'),
('jane_smith', 'jane@example.com', 'Passionate about travel and photography.'),
('alex_wilson', 'alex@example.com', 'Web developer and technology enthusiast.');


INSERT INTO posts (title, content, user_id) VALUES
('Introduction to SQL', 'In this post, we will cover the basics of SQL.', 1),
('Travel Diary: Exploring Europe', 'Sharing my adventures and experiences while traveling through Europe.', 2),
('Building a RESTful API with Node.js', 'A step-by-step guide to creating a RESTful API using Node.js.', 3);

INSERT INTO comments (post_id, user_id, content) VALUES
(1, 2, 'Great introduction! Looking forward to more posts.'),
(1, 3, 'Very informative! SQL is such a powerful language.'),
(2, 1, 'Europe is amazing! I had a similar experience when I visited last year.'),
(3, 3, 'Thanks for the tutorial! I successfully implemented the API following your steps.');

Explanation

users Table: Stores information about users, including their username, email, biography, and the timestamp of account creation.
posts Table: Contains posts created by users, with fields for title, content, the user who created the post, and the creation timestamp. The user_id field establishes a foreign key relationship with the users table.
comments Table: Stores comments made by users on posts. Each comment is associated with a specific post and user. The post_id and user_id fields establish foreign key relationships with the posts and users tables, respectively.

Table: users

This table stores information about users.

Column	Type	Constraints	Description
user_id	SERIAL	PRIMARY KEY	Unique identifier for each user.
username	VARCHAR(50)	UNIQUE NOT NULL	User's username.
email	VARCHAR(100)	UNIQUE NOT NULL	User's email address.
bio	TEXT		User's biography or description.
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Timestamp indicating when the user was created.

Table: posts

This table contains posts created by users.

Column	Type	Constraints	Description
post_id	SERIAL	PRIMARY KEY	Unique identifier for each post.
title	VARCHAR(255)	NOT NULL	Title of the post.
content	TEXT	NOT NULL	Content of the post.
user_id	INT	NOT NULL	Foreign key referencing the user who created the post.
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Timestamp indicating when the post was created.

Table: comments

This table contains comments made by users on posts.

Column	Type	Constraints	Description
comment_id	SERIAL	PRIMARY KEY	Unique identifier for each comment.
post_id	INT	NOT NULL	Foreign key referencing the post being commented on.
user_id	INT	NOT NULL	Foreign key referencing the user who made the comment.
content	TEXT	NOT NULL	Content of the comment.
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Timestamp indicating when the comment was created.

Option 1: Sample Queries if user id is known

Query 1: Retrieve Posts by a Specific User

SELECT 
    p.post_id,
    p.title,
    p.content,
    p.created_at,
    u.username AS author
FROM 
    posts p
JOIN 
    users u ON p.user_id = u.user_id
WHERE 
    u.username = 'john_doe';

Output 1: Posts by Specific User

 post_id |        title        |                    content                     |         created_at         |  author  
---------+---------------------+------------------------------------------------+----------------------------+----------
       1 | Introduction to SQL | In this post, we will cover the basics of SQL. | 2024-02-20 08:31:17.653151 | john_doe

Query 2: Count Comments on Each Post

SELECT 
    p.post_id,
    p.title,
    COUNT(c.comment_id) AS comment_count
FROM 
    posts p
LEFT JOIN 
    comments c ON p.post_id = c.post_id
GROUP BY 
    p.post_id, p.title;

Output 2: Comment Count on Each Post

 post_id |                title                | comment_count 
---------+-------------------------------------+---------------
       3 | Building a RESTful API with Node.js |             1
       2 | Travel Diary: Exploring Europe      |             1
       1 | Introduction to SQL                 |             2

Option 2: Queries by a specific user with a given username

Retrieve posts by a specific user with a given username

SELECT * FROM posts 
JOIN users ON posts.user_id = users.user_id
WHERE users.username = 'jane_smith';

 post_id |             title              |                                content                                | user_id |        created_at         | user_id |  username  |      email       |                   bio                    |         created_at         
---------+--------------------------------+-----------------------------------------------------------------------+---------+---------------------------+---------+------------+------------------+------------------------------------------+----------------------------
       2 | Travel Diary: Exploring Europe | Sharing my adventures and experiences while traveling through Europe. |       2 | 2024-02-21 17:53:22.93689 |       2 | jane_smith | jane@example.com | Passionate about travel and photography. | 2024-02-21 17:53:22.927592

Explanation

SELECT * FROM posts: Selects all columns from the posts table.
JOIN users ON posts.user_id = users.user_id: joins the posts table with the users table based on the user_id column.
WHERE users.username = 'jane_smith': filters the results to only include posts where the username is 'jane_smith'.

Count comments on each post by a specific user with a given username


SELECT p.post_id, p.title, p.content, COUNT(c.comment_id) AS comment_count
FROM posts p
LEFT JOIN comments c ON p.post_id = c.post_id
JOIN users u ON p.user_id = u.user_id
WHERE u.username = 'alex_wilson'
GROUP BY p.post_id, p.title, p.content;

Explanation

This query selects the post ID, title, and content from the table named posts, along with counting the number of comments for each post. its done by the Join Keyword, joining the posts, comments, and users tables, ensuring that each post's author matches the user with the username 'alex_wilson'. Lastly, it groups the results by post ID, title, and content to ensure each post is represented uniquely

 post_id |                title                |                            content                            | comment_count 
---------+-------------------------------------+---------------------------------------------------------------+---------------
       3 | Building a RESTful API with Node.js | A step-by-step guide to creating a RESTful API using Node.js. |             1

Performance Analysis of Queris 1 & 2 from Option 1

Explain Analyze Query 1

EXPLAIN ANALYZE
SELECT 
    p.post_id,
    p.title,
    p.content,
    p.created_at,
    u.username AS author
FROM 
    posts p
JOIN 
    users u ON p.user_id = u.user_id
WHERE 
    u.username = 'john_doe';

Output of Query 1: Performance

 Hash Join  (cost=8.17..19.83 rows=1 width=678) (actual time=0.039..0.052 rows=1 loops=1)
   Hash Cond: (p.user_id = u.user_id)
   ->  Seq Scan on posts p  (cost=0.00..11.30 rows=130 width=564) (actual time=0.005..0.009 rows=3 loops=1)
   ->  Hash  (cost=8.16..8.16 rows=1 width=122) (actual time=0.018..0.021 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Index Scan using users_username_key on users u  (cost=0.14..8.16 rows=1 width=122) (actual time=0.010..0.012 rows=1 loops=1)
               Index Cond: ((username)::text = 'john_doe'::text)
 Planning Time: 0.129 ms
 Execution Time: 0.076 ms

Explain Analyze Query 2

EXPLAIN ANALYZE
SELECT 
    p.post_id,
    p.title,
    COUNT(c.comment_id) AS comment_count
FROM 
    posts p
LEFT JOIN 
    comments c ON p.post_id = c.post_id
GROUP BY 
    p.post_id, p.title;

Output of Query 2: Performance

 HashAggregate  (cost=40.96..42.26 rows=130 width=528) (actual time=0.055..0.069 rows=3 loops=1)
   Group Key: p.post_id
   Batches: 1  Memory Usage: 40kB
   ->  Hash Right Join  (cost=12.93..35.86 rows=1020 width=524) (actual time=0.032..0.054 rows=4 loops=1)
         Hash Cond: (c.post_id = p.post_id)
         ->  Seq Scan on comments c  (cost=0.00..20.20 rows=1020 width=8) (actual time=0.005..0.010 rows=4 loops=1)
         ->  Hash  (cost=11.30..11.30 rows=130 width=520) (actual time=0.018..0.023 rows=3 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Seq Scan on posts p  (cost=0.00..11.30 rows=130 width=520) (actual time=0.006..0.010 rows=3 loops=1)
 Planning Time: 0.088 ms
 Execution Time: 0.109 ms

Database Schema Documentation

Schema users :

Schema posts :

Schema comments :

Poupulating Tables:

Option 1: Sample Queries if user id is known

Query 1: Retrieve Posts by a Specific User

Output 1: Posts by Specific User

Query 2: Count Comments on Each Post

Output 2: Comment Count on Each Post

Option 2: Queries by a specific user with a given username

Retrieve posts by a specific user with a given username

Count comments on each post by a specific user with a given username

Performance Analysis of Queris 1 & 2 from Option 1

Explain Analyze Query 1

Output of Query 1: Performance

Explain Analyze Query 2

Output of Query 2: Performance

Schema `users` :

Schema `posts` :

Schema `comments` :