1. User Table

In this section, we will design the User Table to store user information of all the post authors. The same table can be used to relate the post authors so that all the authors can manage their own posts. Below mentioned is the description of all the columns of the User Table.

--
-- Table structure for table `user`,
-- Change to author as user is a reserved
--

DROP TABLE IF EXISTS blogger.author;
CREATE TABLE blogger.author (
  id SERIAL PRIMARY KEY,
  firstName varchar(50) DEFAULT NULL,
  middleName varchar(50) DEFAULT NULL,
  lastName varchar(50) DEFAULT NULL,
  mobile varchar(15) UNIQUE,
  email varchar(50) UNIQUE,
  passwordHash varchar(32) NOT NULL,
  registeredAt timestamp NOT NULL,
  lastLogin timestamp DEFAULT NULL,
  intro text,
  profile text
);

2. Post Table

In this section, we will design the Post Table to store the post data. Below mentioned is the description of all the columns of the Post Table.

--
-- Table structure for table `post`
--

DROP TABLE IF EXISTS post;
CREATE TABLE post (
  id BIGINT NOT NULL,
  author_id BIGINT NOT NULL,
  parent_id BIGINT,
  title VARCHAR(75) NOT NULL,
  meta_title VARCHAR(100),
  slug VARCHAR(100) NOT NULL,
  summary TEXT,
  published BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP,
  published_at TIMESTAMP,
  content TEXT,
  PRIMARY KEY (id),
  CONSTRAINT fk_post_parent FOREIGN KEY (parent_id) REFERENCES post(id),
  CONSTRAINT fk_post_user FOREIGN KEY (author_id) REFERENCES "user"(id)
);

3. Post Meta -> post_meta

The Post Meta Table can be used to store additional information of a post including the post banner URL etc. Below mentioned is the description of all the columns of the Post Meta Table.

--
-- Table structure for table `post_meta`
--

DROP TABLE IF EXISTS "post_meta";

CREATE TABLE "post_meta" (
  "id" BIGSERIAL NOT NULL,
  "postId" BIGINT NOT NULL,
  "key" VARCHAR(50) COLLATE "pg_catalog"."default" NOT NULL,
  "content" TEXT COLLATE "pg_catalog"."default",
  CONSTRAINT "pk_post_meta_id" PRIMARY KEY ("id"),
  CONSTRAINT "uq_post_meta" UNIQUE ("postId", "key"),
  CONSTRAINT "fk_meta_post" FOREIGN KEY ("postId") REFERENCES "post" ("id")
);

• COLLATE

COLLATE is a clause in SQL used to define the collation (i.e., the set of rules governing how string values are sorted and compared) for a character data type in a database. In the given SQL statement, COLLATE "pg_catalog"."default" is specifying the collation for the key column as the default collation for the PostgreSQL catalog.

By default, if no COLLATE clause is specified, the collation of the column is inherited from the database's default collation. The COLLATE clause can be used to override the default collation and specify a specific collation for the column. This can be useful when working with multilingual databases where different character sets and collations are needed to handle text data correctly.

4. Post Comment --> post_comment

Post Comment Table to is to store the post comments. Below mentioned is the description of all the columns of the Post Comment Table.

--
-- Table structure for table `post_comment`
--

DROP TABLE IF EXISTS post_comment;
CREATE TABLE post_comment (
  id BIGINT NOT NULL,
  post_id BIGINT NOT NULL,
  author_id BIGINT,
  parent_id BIGINT,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP,
  content TEXT,
  PRIMARY KEY (id),
  CONSTRAINT fk_comment_post FOREIGN KEY (post_id) REFERENCES post(id),
  CONSTRAINT fk_comment_user FOREIGN KEY (author_id) REFERENCES "user"(id),
  CONSTRAINT fk_comment_parent FOREIGN KEY (parent_id) REFERENCES post_comment(id)
);

5. Category -->category

We will design the Category Table and Post Category Table to store the post categories and their mappings. Below mentioned is the description of all the columns of the Category Table.

--
-- Table structure for table `category`
--

DROP TABLE IF EXISTS category;
CREATE TABLE category (
  id BIGINT NOT NULL,
  parent_id BIGINT,
  title VARCHAR(75) NOT NULL,
  meta_title VARCHAR(100),
  slug VARCHAR(100) NOT NULL,
  content TEXT,
  PRIMARY KEY (id)
);

6. Post Category Table -> post_category

--
-- Table structure for table `post_category`
--

DROP TABLE IF EXISTS post_category;
CREATE TABLE post_category (
  post_id BIGINT NOT NULL,
  category_id BIGINT NOT NULL,
  PRIMARY KEY (post_id, category_id),
  CONSTRAINT fk_pc_post FOREIGN KEY (post_id) REFERENCES post(id),
  CONSTRAINT fk_pc_category FOREIGN KEY (category_id) REFERENCES category(id)
);

Tag Table and Post Tag Table

Similar to the category and post category tables, we can design the Tag Table and Post Tag Table. The major differences between the Category and Tag are listed below.

The parentId column is not required in the Tag Table.
The count of categories remains low since these can be used to form the Main Menu for navigational purposes. The tags can be more as compared to categories.
Both categories and tags can be used to relate the posts.
One should assign only a few categories to a post whereas tags count can be more.

7. Tag --> tag

--
-- Table structure for table "tag"
--

DROP TABLE IF EXISTS "tag";
CREATE TABLE "tag" (
  "id" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
  "title" VARCHAR(75) COLLATE "pg_catalog"."default" NOT NULL,
  "metaTitle" VARCHAR(100) COLLATE "pg_catalog"."default",
  "slug" VARCHAR(100) COLLATE "pg_catalog"."default" NOT NULL,
  "content" TEXT COLLATE "pg_catalog"."default",
  PRIMARY KEY ("id")
) ;

8. Post Tag --> post_tag

--
-- Table structure for table `post_tag`
--

DROP TABLE IF EXISTS "post_tag";
CREATE TABLE "post_tag" (
  "postId" bigint NOT NULL,
  "tagId" bigint NOT NULL,
  PRIMARY KEY ("postId","tagId"),
  CONSTRAINT "fk_pt_post" FOREIGN KEY ("postId") REFERENCES "post" ("id"),
  CONSTRAINT "fk_pt_tag" FOREIGN KEY ("tagId") REFERENCES "tag" ("id")
);
CREATE INDEX "idx_pt_tag" ON "post_tag" ("tagId");
CREATE INDEX "idx_pt_post" ON "post_tag" ("postId");