Field Descriptions

This document provides detailed descriptions and usage examples of database fields.

General Field Descriptions

Timestamp Fields

All tables include the following timestamp fields:

Field Name	Type	Default Value	Description
created_at	TIMESTAMP	CURRENT_TIMESTAMP	Record creation time
updated_at	TIMESTAMP	Auto-update	Record update time

Status Fields

Different types of status fields:

Table Name	Field Name	Possible Values
users	status	active, inactive
articles	status	draft, published
projects	status	draft, published
categories	status	active, inactive
galleries	status	draft, published
wall_messages	status	pending, approved, rejected
skills	status	active, inactive

Table Field Details

users (User Table)

role Field

Value	Description	Permissions
admin	Administrator	Full backend management permissions
user	Regular user	Basic access permissions

password Field

Uses bcrypt encryption
Storage format: $2b$10$... (60 characters)
Encryption strength: 10 rounds

Example:

sql

-- Original password: 123456
-- Encrypted: $2b$10$lhcSe2CnCAETOw5wcXpFwu8vMydw8h8BPIqOyGnNeIAgvDO3f9VmW

articles (Article Table)

status Field

Value	Description	Visible
draft	Draft	No
published	Published	Yes

is_featured Field

Type: TINYINT(1)
Values: 0 (not featured), 1 (featured)
Used for featured article display on homepage

technologies Field (projects table)

Type: JSON
Storage format: Array string
Example: ["Vue.js", "Node.js", "TypeScript"]

SQL Example:

sql

INSERT INTO projects (title, technologies) 
VALUES ('My Project', '["Vue.js", "Node.js"]');

Query Example:

sql

-- Find projects containing specific technologies
SELECT * FROM projects 
WHERE JSON_CONTAINS(technologies, '"Vue.js"');

settings (System Settings Table)

group_name Field

Group	Purpose
stats	Statistical data (total visits, today's visits, etc.)
storage_settings	Storage settings (local, S3, MinIO, etc.)
analytics	Analytics tracking codes (Google Analytics, etc.)
technologies	Technology tag list

Example Data:

sql

-- Statistical data
INSERT INTO settings (group_name, key_name, key_value)
VALUES 
('stats', 'total_visits', '1000'),
('stats', 'today_visits', '50');

-- Storage settings
INSERT INTO settings (group_name, key_name, key_value)
VALUES 
('storage_settings', 'storage_type', 'local'),
('storage_settings', 'uploadPath', './uploads');

-- Technology tags (JSON format)
INSERT INTO settings (group_name, key_name, key_value)
VALUES
('technologies', 'list', '[{"id": 1, "name": "Vue.js", "icon": "lucide:code-2", "color": "#4fc08d"}]');

files (File Table)

storage_type Field

Value	Description
local	Local file system
s3	AWS S3 or compatible services (MinIO, Alibaba Cloud OSS, etc.)
qiniu	Qiniu Cloud Storage

category Field

Common file categories:

Category	Purpose
cover	Cover images
avatar	User avatars
gallery	Gallery images
resource	Resource files
carousel	Carousel images

file_hash Field

Type: VARCHAR(64)
Format: SHA-256 hash value (64 hexadecimal characters)
Purpose: File deduplication and integrity verification

Generation Example:

javascript

import crypto from 'crypto'

const hash = crypto
  .createHash('sha256')
  .update(fileBuffer)
  .digest('hex')

skills (Skills Table)

level Field

Value	Description	Proficiency Level
beginner	Beginner	Just started
intermediate	Intermediate	Some experience
advanced	Advanced	Proficient
expert	Expert	Mastery

wall_messages (Wall Messages Table)

status Field

Value	Description	Displayed
pending	Pending review	No
approved	Approved	Yes
rejected	Rejected	No

Index Explanation

Single-Column Index

sql

-- Create index on username field
INDEX idx_username (username)

-- Query will use index
SELECT * FROM users WHERE username = 'admin';

Unique Index

sql

-- Unique index on email field
UNIQUE KEY unique_email (email)

-- Prevent duplicate emails
INSERT INTO users (email) VALUES ('test@example.com');
-- Second insert will fail

Composite Index

sql

-- Composite index on date and hour
UNIQUE KEY unique_date_hour (date, hour)

-- Query will use index
SELECT * FROM visit_hourly_statistics 
WHERE date = '2025-01-24' AND hour = 10;

Full-text Index

sql

-- Full-text index on title and description
FULLTEXT KEY idx_title_desc (title, description)

-- Full-text search
SELECT * FROM articles 
WHERE MATCH(title, description) AGAINST('Nuxt 4' IN NATURAL LANGUAGE MODE);

Foreign Key Constraints

Foreign Key Types

sql

-- CASCADE: Automatically delete child records when parent is deleted
FOREIGN KEY (gallery_id) REFERENCES galleries(id) ON DELETE CASCADE

-- SET NULL: Set foreign key to NULL when parent is deleted
FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL

-- RESTRICT: Prevent deletion of parent record
FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE RESTRICT

Usage Examples

sql

-- When deleting a gallery, automatically delete associated images
DELETE FROM galleries WHERE id = 1;

-- When deleting a user, set author_id to NULL in articles
DELETE FROM users WHERE id = 1;

-- If a file is used by resources, prevent deletion
DELETE FROM files WHERE id = 1; -- Will fail if referenced

Data Type Explanations

VARCHAR vs TEXT

Type	Max Length	Purpose
VARCHAR(255)	255 characters	Short text (usernames, emails, etc.)
TEXT	65,535 characters	Medium-length text (introductions, descriptions)
LONGTEXT	4,294,967,295 characters	Long text (article content)

INT vs BIGINT

Type	Range	Purpose
INT	-2^31 ~ 2^31-1	Regular quantities (views, likes)
BIGINT	-2^63 ~ 2^63-1	Large numbers (file sizes)

TIMESTAMP vs DATETIME

Type	Range	Timezone
TIMESTAMP	1970-2038	UTC
DATETIME	1000-9999	Server timezone

Character Set and Collation

sql

-- Database character set
CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci

utf8mb4: Supports full Unicode (including emoji)
utf8mb4_unicode_ci: Case-insensitive collation

Notes

Foreign Key Constraints: Pay attention to dependencies when deleting data
Time Format: Use ISO 8601 format (YYYY-MM-DDTHH:mm:ss.sssZ)
JSON Fields: Use MySQL 5.7+ JSON functions for operations
Index Optimization: Frequently queried fields should have indexes
Character Set: Always use utf8mb4 to support multiple languages

Field Descriptions ​

General Field Descriptions ​

Timestamp Fields ​

Status Fields ​

Table Field Details ​

users (User Table) ​

role Field ​

password Field ​

articles (Article Table) ​

status Field ​

is_featured Field ​

technologies Field (projects table) ​

settings (System Settings Table) ​

group_name Field ​

files (File Table) ​

storage_type Field ​

category Field ​

file_hash Field ​

skills (Skills Table) ​

level Field ​

wall_messages (Wall Messages Table) ​

status Field ​

Index Explanation ​

Single-Column Index ​

Unique Index ​

Composite Index ​

Full-text Index ​

Foreign Key Constraints ​

Foreign Key Types ​

Usage Examples ​

Data Type Explanations ​

VARCHAR vs TEXT ​

INT vs BIGINT ​

TIMESTAMP vs DATETIME ​

Character Set and Collation ​

Notes ​

Related Documents ​

Field Descriptions

General Field Descriptions

Timestamp Fields

Status Fields

Table Field Details

users (User Table)

role Field

password Field

articles (Article Table)

status Field

is_featured Field

technologies Field (projects table)

settings (System Settings Table)

group_name Field

files (File Table)

storage_type Field

category Field

file_hash Field

skills (Skills Table)

level Field

wall_messages (Wall Messages Table)

status Field

Index Explanation

Single-Column Index

Unique Index

Composite Index

Full-text Index

Foreign Key Constraints

Foreign Key Types

Usage Examples

Data Type Explanations

VARCHAR vs TEXT

INT vs BIGINT

TIMESTAMP vs DATETIME

Character Set and Collation

Notes

Related Documents