#2 DBスキーマを作成する+簡単なAPIのテストを実装する

[ju-ki]

ChatGPTでベースを作成。一旦これで作成

Users Table

Stores user information.

Column	Type	Description
id	INT	Primary key
name	VARCHAR(255)	User's name
email	VARCHAR(255)	User's email (unique)
password	VARCHAR(255)	User's hashed password
created_at	TIMESTAMP	Timestamp of creation
updated_at	TIMESTAMP	Timestamp of last update

Posts Table

Stores blog posts.

Column	Type	Description
id	INT	Primary key
user_id	INT	Foreign key to users table
title	VARCHAR(255)	Post title
content	TEXT	Post content
created_at	TIMESTAMP	Timestamp of creation
updated_at	TIMESTAMP	Timestamp of last update

Comments Table

Stores comments on blog posts.

Column	Type	Description
id	INT	Primary key
post_id	INT	Foreign key to posts table
user_id	INT	Foreign key to users table
content	TEXT	Comment content
created_at	TIMESTAMP	Timestamp of creation
updated_at	TIMESTAMP	Timestamp of last update

Categories Table

Stores categories for blog posts.

Column	Type	Description
id	INT	Primary key
name	VARCHAR(255)	Category name
created_at	TIMESTAMP	Timestamp of creation
updated_at	TIMESTAMP	Timestamp of last update

Tags Table

Stores tags for blog posts.

Column	Type	Description
id	INT	Primary key
name	VARCHAR(255)	Tag name
created_at	TIMESTAMP	Timestamp of creation
updated_at	TIMESTAMP	Timestamp of last update

Post_Tags Table

Associative table for many-to-many relationship between posts and tags.

Column	Type	Description
post_id	INT	Foreign key to posts table
tag_id	INT	Foreign key to tags table
created_at	TIMESTAMP	Timestamp of creation

Likes Table

Stores likes on blog posts.

Column	Type	Description
id	INT	Primary key
post_id	INT	Foreign key to posts table
user_id	INT	Foreign key to users table
created_at	TIMESTAMP	Timestamp of creation

Views Table

Stores view counts for blog posts.

Column	Type	Description
id	INT	Primary key
post_id	INT	Foreign key to posts table
view_count	INT	Number of views
created_at	TIMESTAMP	Timestamp of creation
updated_at	TIMESTAMP	Timestamp of last update

Relationships

• Users and Posts: One-to-Many (one user can write many posts)
• Posts and Comments: One-to-Many (one post can have many comments)
• Posts and Categories: Many-to-One (many posts can belong to one category)
• Posts and Tags: Many-to-Many (many posts can have many tags)
• Posts and Likes: One-to-Many (one post can have many likes)
• Posts and Views: One-to-Many (one post can have many views)