If my users tables is
CREATE TABLE users ( user_id SERIAL PRIMARY KEY, -- add first, middle, and last name first_name VARCHAR(50) NULL DEFAULT NULL, middle_name VARCHAR(50) NULL DEFAULT NULL, last_name VARCHAR(50) NULL DEFAULT NULL, -- mobile phone used for login purpose mobile VARCHAR(20) UNIQUE NOT NULL, password VARCHAR(255) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, -- a short intro about the user intro VARCHAR(255) NULL DEFAULT NULL, last_login DATETIME NULL DEFAULT NULL, registered_at TIMESTAMP DEFAULT NOW() );
And posts is as,
```psql
CREATE TABLE posts (
post_id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
published_at TIMESTAMP
);
There is a post bout Guide To Design Database For Blog Management In MySQL@https://mysql.tutorials24x7.com/blog/guide-to-design-a-database-for-blog-management-in-mysql
を知っていますか。 what is the purpose of dedign posts ans psot_meta as
CREATE TABLE `blog`.`post` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`authorId` BIGINT NOT NULL,
`parentId` BIGINT NULL DEFAULT NULL,
`title` VARCHAR(75) NOT NULL,
`metaTitle` VARCHAR(100) NULL,
`slug` VARCHAR(100) NOT NULL,
`summary` TINYTEXT NULL,
`published` TINYINT(1) NOT NULL DEFAULT 0,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NULL DEFAULT NULL,
`publishedAt` DATETIME NULL DEFAULT NULL,
`content` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `uq_slug` (`slug` ASC),
INDEX `idx_post_user` (`authorId` ASC),
CONSTRAINT `fk_post_user`
FOREIGN KEY (`authorId`)
REFERENCES `blog`.`user` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
ALTER TABLE `blog`.`post`
ADD INDEX `idx_post_parent` (`parentId` ASC);
ALTER TABLE `blog`.`post`
ADD CONSTRAINT `fk_post_parent`
FOREIGN KEY (`parentId`)
REFERENCES `blog`.`post` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
and
CREATE TABLE `blog`.`post_meta` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`postId` BIGINT NOT NULL,
`key` VARCHAR(50) NOT NULL,
`content` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_meta_post` (`postId` ASC),
UNIQUE INDEX `uq_post_meta` (`postId` ASC, `key` ASC),
CONSTRAINT `fk_meta_post`
FOREIGN KEY (`postId`)
REFERENCES `blog`.`post` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;