• Sampple PostgreSQL blog database

CREATE TABLE users (
  uid SERIAL PRIMARY KEY,
  username VARCHAR(255) UNIQUE,
  email VARCHAR(255),
  email_verified BOOLEAN,
  date_created DATE,
  last_login DATE
);


CREATE TABLE posts (
  pid SERIAL PRIMARY KEY,
  title VARCHAR(255),
  body VARCHAR,
  user_id INT REFERENCES users(uid),
  author VARCHAR REFERENCES users(username),
  date_created TIMESTAMP
  like_user_id INT[] DEFAULT ARRAY[]::INT[],
  likes INT DEFAULT 0
);

CREATE TABLE comments (
  cid SERIAL PRIMARY KEY,
  comment VARCHAR(255),
  author VARCHAR REFERENCES users(username),
  user_id INT REFERENCES users(uid),
  post_id INT REFERENCES posts(pid),
  date_created TIMESTAMP
);

• Sugested by ChatGPT

CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name TEXT NOT NULL,
    email TEXT NOT NULL
);

CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    publish_date TIMESTAMP NOT NULL,
    author_id INTEGER NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors (author_id)
);

CREATE TABLE comments (
    comment_id SERIAL PRIMARY KEY,
    comment_text TEXT NOT NULL,
    comment_date TIMESTAMP NOT NULL,
    post_id INTEGER NOT NULL,
    author_id INTEGER NOT NULL,
    FOREIGN KEY (post_id) REFERENCES posts (post_id),
    FOREIGN KEY (author_id) REFERENCES authors (author_id)
);

This schema has three tables: authors, posts, and comments. The authors table stores information about the blog authors, including their names and email addresses. The posts table stores the blog posts, including the title, content, publish date, and the ID of the author who wrote the post. The comments table stores the comments on each post, including the comment text, the date it was posted, the ID of the post it belongs to, and the ID of the author who posted the comment.

You can populate the tables with sample data using INSERT statements. Here's an example:

INSERT INTO authors (author_name, email) VALUES ('John Smith', 'john@example.com');
INSERT INTO authors (author_name, email) VALUES ('Jane Doe', 'jane@example.com');

INSERT INTO posts (title, content, publish_date, author_id) VALUES ('My first blog post', 'Lorem ipsum dolor sit amet...', '2022-01-01 12:00:00', 1);
INSERT INTO posts (title, content, publish_date, author_id) VALUES ('Another blog post', 'Lorem ipsum dolor sit amet...', '2022-01-15 12:00:00', 2);

INSERT INTO comments (comment_text, comment_date, post_id, author_id) VALUES ('Great post!', '2022-01-02 13:00:00', 1, 2);
INSERT INTO comments (comment_text, comment_date, post_id, author_id) VALUES ('Thanks for sharing!', '2022-01-16 14:00:00', 2, 1);

This populates the authors table with two authors, John Smith and Jane Doe. It then creates two blog posts, one by each author. Finally, it adds two comments, one to each post, by the opposite author