Table Structure Description

1. users (User Table)

Description

Stores system user information, including administrators and regular users.

Field Description

Field Name	Type	Description	Constraints
id	INT	User ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
username	VARCHAR(50)	Username	UNIQUE, NOT NULL
email	VARCHAR(100)	Email	UNIQUE, NOT NULL
password	VARCHAR(255)	Password hash	NOT NULL
nickname	VARCHAR(100)	Nickname	-
avatar	VARCHAR(255)	Avatar URL	-
bio	TEXT	Personal introduction	-
role	ENUM	User role	DEFAULT 'user', VALUES('admin', 'user')
status	ENUM	User status	DEFAULT 'active', VALUES('active', 'inactive')
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes

• idx_username: username
• idx_email: email

---

2. categories (Category Table)

Description

Stores article category information.

Field Description

Field Name	Type	Description	Constraints
id	INT	Category ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
name	VARCHAR(100)	Category name	NOT NULL
slug	VARCHAR(100)	Category slug	UNIQUE, NOT NULL
description	TEXT	Category description	-
color	VARCHAR(7)	Category color	DEFAULT '#1890ff'
icon	VARCHAR(50)	Category icon	DEFAULT 'fa:folder'
sort_order	INT	Sort order	DEFAULT 0
status	ENUM	Status	DEFAULT 'active', VALUES('active', 'inactive')
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes

• idx_slug: slug
• idx_sort: sort_order

---

3. articles (Article Table)

Description

Stores blog article information.

Field Description

Field Name	Type	Description	Constraints
id	INT	Article ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
title	VARCHAR(200)	Article title	NOT NULL
content	LONGTEXT	Article content	-
summary	VARCHAR(500)	Article summary	-
cover_image	VARCHAR(500)	Cover image URL	-
view_count	INT	View count	DEFAULT 0
like_count	INT	Like count	DEFAULT 0
status	ENUM	Publication status	DEFAULT 'draft', VALUES('draft', 'published')
is_featured	TINYINT(1)	Whether featured	DEFAULT 0
allow_comment	TINYINT(1)	Whether comments allowed	DEFAULT 1
author_id	INT	Author ID	FOREIGN KEY -> users(id)
category_id	INT	Category ID	FOREIGN KEY -> categories(id)
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes

• idx_status: status
• idx_featured: is_featured
• idx_view_count: view_count
• idx_created_at: created_at
• idx_author: author_id
• idx_category: category_id

---

4. projects (Project Table)

Description

Stores project portfolio information.

Field Description

Field Name	Type	Description	Constraints
id	INT	Project ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
title	VARCHAR(200)	Project title	NOT NULL
description	TEXT	Project description	-
content	LONGTEXT	Project detail content	-
status	ENUM	Project status	DEFAULT 'draft', VALUES('draft', 'published')
is_featured	TINYINT(1)	Whether special project	DEFAULT 0
category	INT	Project category	NOT NULL
technologies	JSON	Technology stack	-
cover_image_id	INT	Cover image ID	FOREIGN KEY -> files(id)
view_count	INT	View count	DEFAULT 0
like_count	INT	Like count	DEFAULT 0
demo_url	VARCHAR(255)	Demo URL	-
github_url	VARCHAR(255)	GitHub URL	-
role	VARCHAR(100)	Job role	-
sort_order	INT	Sort order	DEFAULT 0
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes

• idx_category: category
• idx_cover_image: cover_image_id
• idx_sort_order: sort_order
• idx_title_desc: FULLTEXT(title, description)

---

5. galleries (Gallery Table)

Description

Stores gallery information.

Field Description

Field Name	Type	Description	Constraints
id	INT	Gallery ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
title	VARCHAR(200)	Gallery title	NOT NULL
description	TEXT	Gallery description	-
cover_image	VARCHAR(500)	Cover image URL	-
view_count	INT	View count	DEFAULT 0
comment_count	INT	Comment count	DEFAULT 0
status	ENUM	Publication status	DEFAULT 'draft', VALUES('draft', 'published')
is_featured	TINYINT(1)	Whether featured	DEFAULT 0
sort_order	INT	Sort order	DEFAULT 0
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes

• idx_status: status
• idx_featured: is_featured
• idx_view_count: view_count
• idx_sort_order: sort_order
• idx_created_at: created_at

---

6. resources (Resource Table)

Description

Stores resource download information.

Field Description

Field Name	Type	Description	Constraints
id	INT	Resource ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
title	VARCHAR(200)	Resource title	NOT NULL
description	TEXT	Resource description	-
content	LONGTEXT	Resource detail content	-
category_id	INT	Resource category ID	FOREIGN KEY -> resource_categories(id)
subcategory	VARCHAR(100)	Resource subcategory	-
file_id	INT	File ID	FOREIGN KEY -> files(id)
cover_image_id	INT	Cover image ID	FOREIGN KEY -> files(id)
file_type	VARCHAR(50)	File type	-
file_size	BIGINT	File size (bytes)	-
download_code	VARCHAR(20)	Download code	UNIQUE
download_count	INT	Download count	DEFAULT 0
view_count	INT	View count	DEFAULT 0
like_count	INT	Like count	DEFAULT 0
status	ENUM	Publication status	DEFAULT 'draft', VALUES('draft', 'published')
is_featured	TINYINT(1)	Whether featured	DEFAULT 0
sort_order	INT	Sort order	DEFAULT 0
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes

• idx_category: category_id
• idx_file: file_id
• idx_cover: cover_image_id
• idx_download_code: download_code
• idx_status: status
• idx_sort: sort_order
• idx_title_desc: FULLTEXT(title, description)

---

7. skills (Skill Table)

Description

Stores skill item information.

Field Description

Field Name	Type	Description	Constraints
id	INT	Skill ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
category_id	INT	Category ID	FOREIGN KEY -> skill_categories(id), NOT NULL
name	VARCHAR(100)	Skill name	NOT NULL
level	ENUM	Skill level	DEFAULT 'intermediate', VALUES('beginner', 'intermediate', 'advanced', 'expert')
color	VARCHAR(7)	Skill color	DEFAULT '#1890ff'
description	TEXT	Skill description	-
status	ENUM	Status	DEFAULT 'active', VALUES('active', 'inactive')
sort_order	INT	Sort order	DEFAULT 0
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes

• idx_category: category_id
• idx_sort: sort_order

---

8. files (File Table)

Description

Stores file information, supporting multiple storage methods.

Field Description

Field Name	Type	Description	Constraints
id	INT	File ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
original_name	VARCHAR(255)	Original filename	NOT NULL
file_name	VARCHAR(255)	Storage filename	NOT NULL
file_path	VARCHAR(500)	File relative path	NOT NULL
file_size	BIGINT	File size (bytes)	NOT NULL
mime_type	VARCHAR(100)	File type	NOT NULL
storage_type	VARCHAR(100)	Storage type	DEFAULT 'local'
category	VARCHAR(100)	File category	NOT NULL
bucket_name	VARCHAR(100)	Storage bucket name	-
file_hash	VARCHAR(64)	File hash value (SHA-256)	NOT NULL
status	ENUM	File status	DEFAULT 'active', VALUES('active', 'deleted')
created_by	INT	Upload user ID	FOREIGN KEY -> users(id)
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes

• idx_category: category
• idx_storage_type: storage_type
• idx_created_by: created_by
• idx_created_at: created_at
• idx_file_hash: file_hash

---

9. carousels (Carousel Table)

Description

Stores homepage carousel information.

Field Description

Field Name	Type	Description	Constraints
id	INT	Carousel ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
title	VARCHAR(200)	Carousel title	NOT NULL
subtitle	VARCHAR(200)	Carousel subtitle	-
description	TEXT	Carousel description	-
cover	VARCHAR(500)	Cover image URL	NOT NULL
show_title	TINYINT(1)	Whether to show title	DEFAULT 1
primary_text	VARCHAR(100)	Primary button text	DEFAULT 'Learn more'
primary_link	VARCHAR(500)	Primary button link	-
secondary_text	VARCHAR(100)	Secondary button text	DEFAULT 'View details'
secondary_link	VARCHAR(500)	Secondary button link	-
sort_order	INT	Sort order	DEFAULT 0
status	ENUM	Status	DEFAULT 'active', VALUES('active', 'inactive')
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes

• idx_status: status
• idx_sort_order: sort_order
• idx_created_at: created_at

---

10. wall_messages (Message Table)

Description

Stores message information.

Field Description

Field Name	Type	Description	Constraints
id	INT	Message ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
content	TEXT	Message content	NOT NULL
author_name	VARCHAR(100)	Author name	DEFAULT 'Anonymous'
author_email	VARCHAR(100)	Author email	-
author_website	VARCHAR(200)	Author website	-
label	VARCHAR(50)	Message label	DEFAULT 'Message'
bg_color	VARCHAR(50)	Background color	DEFAULT 'rgba(146, 230, 245, 0.3)'
like_count	INT	Like count	DEFAULT 0
comment_count	INT	Comment count	DEFAULT 0
status	ENUM	Message status	DEFAULT 'pending', VALUES('pending', 'approved', 'rejected')
ip_address	VARCHAR(50)	IP address	-
user_agent	VARCHAR(500)	User agent	-
sort_order	INT	Sort order	DEFAULT 0
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes

• idx_status: status
• idx_label: label
• idx_created_at: created_at
• idx_sort_order: sort_order

---

11. settings (System Settings Table)

Description

Stores system configuration information.

Field Description

Field Name	Type	Description	Constraints
id	INT	Settings ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
group_name	VARCHAR(100)	Settings group	NOT NULL
key_name	VARCHAR(100)	Settings key	NOT NULL
key_value	TEXT	Settings value	-
description	VARCHAR(255)	Settings description	-
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Unique Index

• unique_group_key: (group_name, key_name)

---

12. skill_categories (Skill Category Table)

Description

Stores skill category information.

Field Description

Field Name	Type	Description	Constraints
id	INT	Category ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
name	VARCHAR(100)	Skill category name	NOT NULL
icon	VARCHAR(50)	Category icon	DEFAULT 'fa:code'
description	TEXT	Category description	-
color	VARCHAR(7)	Category color	DEFAULT '#1890ff'
status	ENUM	Status	DEFAULT 'active', VALUES('active', 'inactive')
sort_order	INT	Sort order	DEFAULT 0
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes

• idx_sort: sort_order

---

13. resource_categories (Resource Category Table)

Description

Stores resource category information.

Field Description

Field Name	Type	Description	Constraints
id	INT	Category ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
name	VARCHAR(100)	Category name	NOT NULL
slug	VARCHAR(100)	Category slug	UNIQUE, NOT NULL
description	TEXT	Category description	-
icon	VARCHAR(50)	Category icon	DEFAULT 'fa:folder'
color	VARCHAR(7)	Category color	DEFAULT '#1890ff'
sort_order	INT	Sort order	DEFAULT 0
status	ENUM	Status	DEFAULT 'active', VALUES('active', 'inactive')
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes

• idx_slug: slug
• idx_sort: sort_order

---

14. gallery_images (Gallery Image Table)

Description

Stores images within galleries.

Field Description

Field Name	Type	Description	Constraints
id	INT	Image ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
gallery_id	INT	Gallery ID	FOREIGN KEY -> galleries(id), NOT NULL
title	VARCHAR(200)	Image title	-
description	TEXT	Image description	-
file_id	INT	File ID	FOREIGN KEY -> files(id), NOT NULL
sort_order	INT	Sort order	DEFAULT 0
status	ENUM	Status	DEFAULT 'active', VALUES('active', 'inactive')
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes

• idx_gallery_id: gallery_id
• idx_file_id: file_id
• idx_sort_order: sort_order

---

15. visit_hourly_statistics (Hourly Visit Statistics Table)

Description

Stores hourly visit statistics information.

Field Description

Field Name	Type	Description	Constraints
id	INT	Statistics ID (Primary Key)	PRIMARY KEY, AUTO_INCREMENT
date	DATE	Statistics date	NOT NULL
hour	TINYINT	Hour (0-23)	NOT NULL
total_visits	INT	Total visits	DEFAULT 0
increment_visits	INT	Incremental visits	DEFAULT 0
created_at	TIMESTAMP	Creation time	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	Update time	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Unique Index

• unique_date_hour: (date, hour)

Indexes

• idx_date_hour: (date, hour)
• idx_hour: hour

---

Foreign Key Relationship Summary

Child Table	Foreign Key Field	Parent Table
articles	author_id	users
articles	category_id	categories
projects	cover_image_id	files
resources	category_id	resource_categories
resources	file_id	files
resources	cover_image_id	files
skills	category_id	skill_categories
files	created_by	users
gallery_images	gallery_id	galleries
gallery_images	file_id	files

For detailed field descriptions, please refer to Field Descriptions.