Kindergarten Management System Database Design: A Complete Guide

Table of Contents

Kindergarten Management System - This article is part of a series.

§ : This article

Database design is the core foundation of a kindergarten management system. This article provides detailed table structure designs for each business module based on the functional requirements outlined in the “Kindergarten Management System Design Guide”. Design principles follow: data integrity, query efficiency, flexible extensibility, and privacy security.

1. Database Design Principles
#

1.1 Naming Conventions
#

Type	Convention	Example
Table name	Lowercase with underscores, module prefix	`stu_student`, `fin_payment`
Field name	Lowercase with underscores, semantic clarity	`student_name`, `created_at`
Primary key	`id`, auto-increment or snowflake ID	`id BIGINT PRIMARY KEY`
Foreign key	`{related_table}_id`	`class_id`, `student_id`
Index	`idx_{table}_{field}`	`idx_stu_student_class_id`
Unique index	`uk_{table}_{field}`	`uk_sys_user_username`

1.2 Common Fields
#

Every table includes these common fields:

`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
`created_by` BIGINT COMMENT 'Creator ID',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
`updated_by` BIGINT COMMENT 'Updater ID',
`deleted` TINYINT NOT NULL DEFAULT 0 COMMENT 'Soft delete flag: 0-active, 1-deleted'

1.3 Module Prefixes
#

├── sys_    # System management (users, roles, permissions)
├── stu_    # Student management (student info, family info)
├── cls_    # Class management (classes, teachers)
├── att_    # Attendance management (attendance, leave, pickup)
├── tch_    # Teaching management (courses, growth records)
├── meal_   # Meal management (menus, procurement)
├── fin_    # Financial management (fees, payments)
├── msg_    # Message notification (notices, messages)
└── health_ # Health management (checkups, vaccines)

2. System Management Module
#

2.1 User Table (sys_user)
#

Stores all user account information including directors, teachers, parents, and logistics staff.

CREATE TABLE `sys_user` (
  `id` BIGINT PRIMARY KEY COMMENT 'User ID',
  `username` VARCHAR(50) NOT NULL COMMENT 'Username/Phone number',
  `password` VARCHAR(255) NOT NULL COMMENT 'Password (encrypted)',
  `real_name` VARCHAR(50) NOT NULL COMMENT 'Real name',
  `phone` VARCHAR(20) COMMENT 'Phone number',
  `email` VARCHAR(100) COMMENT 'Email',
  `avatar` VARCHAR(255) COMMENT 'Avatar URL',
  `gender` TINYINT DEFAULT 0 COMMENT 'Gender: 0-unknown, 1-male, 2-female',
  `user_type` TINYINT NOT NULL COMMENT 'User type: 1-director, 2-teacher, 3-parent, 4-logistics, 5-nurse, 6-admin',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT 'Status: 0-disabled, 1-active',
  `last_login_at` DATETIME COMMENT 'Last login time',
  `last_login_ip` VARCHAR(50) COMMENT 'Last login IP',
  `remark` VARCHAR(500) COMMENT 'Remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_sys_user_username` (`username`),
  UNIQUE KEY `uk_sys_user_phone` (`phone`),
  KEY `idx_sys_user_type` (`user_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User table';

2.2 Role Table (sys_role)
#

CREATE TABLE `sys_role` (
  `id` BIGINT PRIMARY KEY COMMENT 'Role ID',
  `role_name` VARCHAR(50) NOT NULL COMMENT 'Role name',
  `role_code` VARCHAR(50) NOT NULL COMMENT 'Role code',
  `description` VARCHAR(255) COMMENT 'Role description',
  `sort` INT DEFAULT 0 COMMENT 'Sort order',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT 'Status: 0-disabled, 1-active',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_sys_role_code` (`role_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Role table';

2.3 User-Role Association Table (sys_user_role)
#

CREATE TABLE `sys_user_role` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `user_id` BIGINT NOT NULL COMMENT 'User ID',
  `role_id` BIGINT NOT NULL COMMENT 'Role ID',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_sys_user_role` (`user_id`, `role_id`),
  KEY `idx_sys_user_role_role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User-role association table';

2.4 Permission Table (sys_permission)
#

CREATE TABLE `sys_permission` (
  `id` BIGINT PRIMARY KEY COMMENT 'Permission ID',
  `parent_id` BIGINT DEFAULT 0 COMMENT 'Parent permission ID',
  `permission_name` VARCHAR(50) NOT NULL COMMENT 'Permission name',
  `permission_code` VARCHAR(100) NOT NULL COMMENT 'Permission code',
  `permission_type` TINYINT NOT NULL COMMENT 'Type: 1-menu, 2-button, 3-API',
  `path` VARCHAR(255) COMMENT 'Route path',
  `component` VARCHAR(255) COMMENT 'Component path',
  `icon` VARCHAR(100) COMMENT 'Icon',
  `sort` INT DEFAULT 0 COMMENT 'Sort order',
  `visible` TINYINT DEFAULT 1 COMMENT 'Visible: 0-no, 1-yes',
  `status` TINYINT DEFAULT 1 COMMENT 'Status: 0-disabled, 1-active',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_sys_permission_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Permission table';

2.5 Role-Permission Association Table (sys_role_permission)
#

CREATE TABLE `sys_role_permission` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `role_id` BIGINT NOT NULL COMMENT 'Role ID',
  `permission_id` BIGINT NOT NULL COMMENT 'Permission ID',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_sys_role_permission` (`role_id`, `permission_id`),
  KEY `idx_sys_role_permission_perm` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Role-permission association table';

3. Student Management Module
#

3.1 Student Information Table (stu_student)
#

Core business table storing student basic information.

CREATE TABLE `stu_student` (
  `id` BIGINT PRIMARY KEY COMMENT 'Student ID',
  `student_no` VARCHAR(50) COMMENT 'Student number',
  `student_name` VARCHAR(50) NOT NULL COMMENT 'Student name',
  `gender` TINYINT NOT NULL COMMENT 'Gender: 1-male, 2-female',
  `birth_date` DATE NOT NULL COMMENT 'Birth date',
  `id_card` VARCHAR(18) COMMENT 'ID card number (encrypted)',
  `nation` VARCHAR(20) COMMENT 'Ethnicity',
  `native_place` VARCHAR(100) COMMENT 'Native place',
  `household_type` TINYINT COMMENT 'Household type: 1-local, 2-non-local',
  `household_address` VARCHAR(255) COMMENT 'Household registration address',
  `current_address` VARCHAR(255) COMMENT 'Current address',
  `photo` VARCHAR(255) COMMENT 'Photo URL',
  `face_feature` TEXT COMMENT 'Face feature data (for facial recognition)',
  `blood_type` VARCHAR(5) COMMENT 'Blood type: A, B, AB, O',
  `enrollment_date` DATE COMMENT 'Enrollment date',
  `graduation_date` DATE COMMENT 'Graduation date',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT 'Status: 1-enrolled, 2-graduated, 3-transferred, 4-withdrawn',
  `source` TINYINT COMMENT 'Source: 1-online registration, 2-on-site, 3-transfer',
  `remark` VARCHAR(500) COMMENT 'Remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_stu_student_no` (`student_no`),
  KEY `idx_stu_student_status` (`status`),
  KEY `idx_stu_student_enrollment` (`enrollment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Student information table';

3.2 Family Member Table (stu_family_member)
#

Stores information about parents, grandparents, and other family members.

CREATE TABLE `stu_family_member` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `student_id` BIGINT NOT NULL COMMENT 'Student ID',
  `member_type` TINYINT NOT NULL COMMENT 'Member type: 1-father, 2-mother, 3-paternal grandfather, 4-paternal grandmother, 5-maternal grandfather, 6-maternal grandmother, 7-other',
  `member_name` VARCHAR(50) NOT NULL COMMENT 'Member name',
  `relation` VARCHAR(20) COMMENT 'Relationship with student',
  `id_card` VARCHAR(18) COMMENT 'ID card number (encrypted)',
  `phone` VARCHAR(20) COMMENT 'Phone number',
  `work_unit` VARCHAR(100) COMMENT 'Work unit',
  `occupation` VARCHAR(50) COMMENT 'Occupation',
  `education` VARCHAR(20) COMMENT 'Education level',
  `is_emergency` TINYINT DEFAULT 0 COMMENT 'Emergency contact: 0-no, 1-yes',
  `is_pickup` TINYINT DEFAULT 0 COMMENT 'Pickup person: 0-no, 1-yes',
  `face_feature` TEXT COMMENT 'Face feature data',
  `photo` VARCHAR(255) COMMENT 'Photo URL',
  `user_id` BIGINT COMMENT 'Associated user ID',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_stu_family_member_student` (`student_id`),
  KEY `idx_stu_family_member_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Family member table';

3.3 Student-Class Association Table (stu_student_class)
#

Records student class assignment history, supporting promotion and transfer tracking.

CREATE TABLE `stu_student_class` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `student_id` BIGINT NOT NULL COMMENT 'Student ID',
  `class_id` BIGINT NOT NULL COMMENT 'Class ID',
  `start_date` DATE NOT NULL COMMENT 'Start date',
  `end_date` DATE COMMENT 'End date',
  `change_type` TINYINT NOT NULL COMMENT 'Change type: 1-enrollment assignment, 2-promotion, 3-transfer',
  `change_reason` VARCHAR(255) COMMENT 'Change reason',
  `is_current` TINYINT DEFAULT 1 COMMENT 'Current class: 0-no, 1-yes',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_stu_student_class_student` (`student_id`),
  KEY `idx_stu_student_class_class` (`class_id`),
  KEY `idx_stu_student_class_current` (`is_current`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Student-class association table';

4. Class Management Module
#

4.1 Class Table (cls_class)
#

CREATE TABLE `cls_class` (
  `id` BIGINT PRIMARY KEY COMMENT 'Class ID',
  `class_name` VARCHAR(50) NOT NULL COMMENT 'Class name',
  `class_code` VARCHAR(50) COMMENT 'Class code',
  `class_type` TINYINT NOT NULL COMMENT 'Class type: 1-nursery, 2-junior, 3-middle, 4-senior',
  `grade_year` INT COMMENT 'Grade year (e.g., 2024)',
  `capacity` INT NOT NULL DEFAULT 30 COMMENT 'Class capacity',
  `current_count` INT DEFAULT 0 COMMENT 'Current student count',
  `classroom` VARCHAR(50) COMMENT 'Classroom location',
  `head_teacher_id` BIGINT COMMENT 'Head teacher ID',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT 'Status: 0-closed, 1-active',
  `description` VARCHAR(255) COMMENT 'Class description',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_cls_class_code` (`class_code`),
  KEY `idx_cls_class_type` (`class_type`),
  KEY `idx_cls_class_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Class table';

4.2 Teacher Information Table (cls_teacher)
#

CREATE TABLE `cls_teacher` (
  `id` BIGINT PRIMARY KEY COMMENT 'Teacher ID',
  `user_id` BIGINT NOT NULL COMMENT 'Associated user ID',
  `teacher_no` VARCHAR(50) COMMENT 'Employee number',
  `teacher_name` VARCHAR(50) NOT NULL COMMENT 'Teacher name',
  `gender` TINYINT NOT NULL COMMENT 'Gender: 1-male, 2-female',
  `birth_date` DATE COMMENT 'Birth date',
  `id_card` VARCHAR(18) COMMENT 'ID card number (encrypted)',
  `phone` VARCHAR(20) COMMENT 'Phone number',
  `education` VARCHAR(20) COMMENT 'Education level',
  `major` VARCHAR(50) COMMENT 'Major',
  `graduate_school` VARCHAR(100) COMMENT 'Graduate school',
  `entry_date` DATE COMMENT 'Entry date',
  `leave_date` DATE COMMENT 'Leave date',
  `position_type` TINYINT NOT NULL COMMENT 'Position type: 1-head teacher, 2-assistant teacher, 3-caregiver, 4-nurse, 5-logistics',
  `qualification_no` VARCHAR(50) COMMENT 'Teacher qualification certificate number',
  `qualification_type` VARCHAR(50) COMMENT 'Certificate type',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT 'Status: 0-left, 1-active',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_cls_teacher_user` (`user_id`),
  KEY `idx_cls_teacher_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Teacher information table';

4.3 Teacher-Class Association Table (cls_teacher_class)
#

CREATE TABLE `cls_teacher_class` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `teacher_id` BIGINT NOT NULL COMMENT 'Teacher ID',
  `class_id` BIGINT NOT NULL COMMENT 'Class ID',
  `role_type` TINYINT NOT NULL COMMENT 'Role type: 1-head teacher, 2-primary teacher, 3-assistant teacher, 4-caregiver',
  `start_date` DATE NOT NULL COMMENT 'Start date',
  `end_date` DATE COMMENT 'End date',
  `is_current` TINYINT DEFAULT 1 COMMENT 'Current: 0-no, 1-yes',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_cls_teacher_class_teacher` (`teacher_id`),
  KEY `idx_cls_teacher_class_class` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Teacher-class association table';

5. Attendance Management Module
#

5.1 Student Attendance Table (att_student_attendance)
#

CREATE TABLE `att_student_attendance` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `student_id` BIGINT NOT NULL COMMENT 'Student ID',
  `class_id` BIGINT NOT NULL COMMENT 'Class ID',
  `attendance_date` DATE NOT NULL COMMENT 'Attendance date',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT 'Status: 1-present, 2-leave, 3-late, 4-early leave, 5-absent',
  `arrival_time` TIME COMMENT 'Arrival time',
  `arrival_type` TINYINT COMMENT 'Arrival method: 1-card, 2-facial recognition, 3-manual',
  `arrival_photo` VARCHAR(255) COMMENT 'Arrival photo',
  `leave_time` TIME COMMENT 'Departure time',
  `leave_type` TINYINT COMMENT 'Departure method: 1-card, 2-facial recognition, 3-manual',
  `leave_photo` VARCHAR(255) COMMENT 'Departure photo',
  `temperature` DECIMAL(4,2) COMMENT 'Morning check temperature',
  `health_status` TINYINT COMMENT 'Morning check health: 1-normal, 2-abnormal',
  `health_remark` VARCHAR(255) COMMENT 'Morning check remark',
  `remark` VARCHAR(255) COMMENT 'Remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_att_student_attendance` (`student_id`, `attendance_date`),
  KEY `idx_att_student_attendance_date` (`attendance_date`),
  KEY `idx_att_student_attendance_class` (`class_id`, `attendance_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Student attendance table';

5.2 Leave Request Table (att_leave_request)
#

CREATE TABLE `att_leave_request` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `student_id` BIGINT NOT NULL COMMENT 'Student ID',
  `applicant_id` BIGINT NOT NULL COMMENT 'Applicant ID (parent)',
  `leave_type` TINYINT NOT NULL COMMENT 'Leave type: 1-sick, 2-personal',
  `start_date` DATE NOT NULL COMMENT 'Start date',
  `end_date` DATE NOT NULL COMMENT 'End date',
  `days` DECIMAL(4,1) NOT NULL COMMENT 'Number of days',
  `reason` VARCHAR(500) COMMENT 'Leave reason',
  `attachment` VARCHAR(255) COMMENT 'Attachment (e.g., medical certificate photo)',
  `status` TINYINT NOT NULL DEFAULT 0 COMMENT 'Status: 0-pending, 1-approved, 2-rejected, 3-cancelled',
  `approver_id` BIGINT COMMENT 'Approver ID',
  `approve_time` DATETIME COMMENT 'Approval time',
  `approve_remark` VARCHAR(255) COMMENT 'Approval remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_att_leave_request_student` (`student_id`),
  KEY `idx_att_leave_request_status` (`status`),
  KEY `idx_att_leave_request_date` (`start_date`, `end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Leave request table';

5.3 Pickup Record Table (att_pickup_record)
#

CREATE TABLE `att_pickup_record` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `student_id` BIGINT NOT NULL COMMENT 'Student ID',
  `pickup_date` DATE NOT NULL COMMENT 'Pickup date',
  `pickup_type` TINYINT NOT NULL COMMENT 'Pickup type: 1-arrival, 2-departure',
  `pickup_time` TIME NOT NULL COMMENT 'Pickup time',
  `pickup_person_id` BIGINT COMMENT 'Pickup person ID (family member)',
  `pickup_person_name` VARCHAR(50) COMMENT 'Pickup person name',
  `pickup_relation` VARCHAR(20) COMMENT 'Relationship with student',
  `verify_type` TINYINT COMMENT 'Verification method: 1-card, 2-facial recognition, 3-password',
  `verify_photo` VARCHAR(255) COMMENT 'Verification photo',
  `operator_id` BIGINT COMMENT 'Operator ID (teacher)',
  `remark` VARCHAR(255) COMMENT 'Remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_att_pickup_record_student` (`student_id`),
  KEY `idx_att_pickup_record_date` (`pickup_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Pickup record table';

5.4 Teacher Attendance Table (att_teacher_attendance)
#

CREATE TABLE `att_teacher_attendance` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `teacher_id` BIGINT NOT NULL COMMENT 'Teacher ID',
  `attendance_date` DATE NOT NULL COMMENT 'Attendance date',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT 'Status: 1-normal, 2-late, 3-early leave, 4-leave, 5-absent',
  `clock_in_time` TIME COMMENT 'Clock in time',
  `clock_in_type` TINYINT COMMENT 'Clock in method: 1-facial recognition, 2-fingerprint, 3-card',
  `clock_out_time` TIME COMMENT 'Clock out time',
  `clock_out_type` TINYINT COMMENT 'Clock out method',
  `work_hours` DECIMAL(4,1) COMMENT 'Work hours',
  `remark` VARCHAR(255) COMMENT 'Remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_att_teacher_attendance` (`teacher_id`, `attendance_date`),
  KEY `idx_att_teacher_attendance_date` (`attendance_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Teacher attendance table';

6. Teaching Management Module
#

6.1 Course Table (tch_course)
#

CREATE TABLE `tch_course` (
  `id` BIGINT PRIMARY KEY COMMENT 'Course ID',
  `course_name` VARCHAR(100) NOT NULL COMMENT 'Course name',
  `course_code` VARCHAR(50) COMMENT 'Course code',
  `course_type` TINYINT NOT NULL COMMENT 'Course type: 1-health, 2-language, 3-social, 4-science, 5-art, 6-special',
  `target_age` TINYINT COMMENT 'Target age: 1-nursery, 2-junior, 3-middle, 4-senior',
  `description` VARCHAR(500) COMMENT 'Course description',
  `objectives` TEXT COMMENT 'Teaching objectives',
  `duration` INT COMMENT 'Course duration (minutes)',
  `status` TINYINT DEFAULT 1 COMMENT 'Status: 0-disabled, 1-active',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_tch_course_type` (`course_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Course table';

6.2 Course Schedule Table (tch_course_schedule)
#

CREATE TABLE `tch_course_schedule` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `class_id` BIGINT NOT NULL COMMENT 'Class ID',
  `course_id` BIGINT NOT NULL COMMENT 'Course ID',
  `teacher_id` BIGINT NOT NULL COMMENT 'Teacher ID',
  `week_day` TINYINT NOT NULL COMMENT 'Day of week: 1-7',
  `start_time` TIME NOT NULL COMMENT 'Start time',
  `end_time` TIME NOT NULL COMMENT 'End time',
  `location` VARCHAR(50) COMMENT 'Location',
  `effective_date` DATE COMMENT 'Effective date',
  `expiry_date` DATE COMMENT 'Expiry date',
  `status` TINYINT DEFAULT 1 COMMENT 'Status: 0-inactive, 1-active',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_tch_course_schedule_class` (`class_id`),
  KEY `idx_tch_course_schedule_teacher` (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Course schedule table';

6.3 Growth Record Table (tch_growth_record)
#

CREATE TABLE `tch_growth_record` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `student_id` BIGINT NOT NULL COMMENT 'Student ID',
  `class_id` BIGINT NOT NULL COMMENT 'Class ID',
  `teacher_id` BIGINT NOT NULL COMMENT 'Teacher ID',
  `record_date` DATE NOT NULL COMMENT 'Record date',
  `record_type` TINYINT NOT NULL COMMENT 'Record type: 1-daily activity, 2-learning, 3-social, 4-habits, 5-physical',
  `title` VARCHAR(100) COMMENT 'Title',
  `content` TEXT NOT NULL COMMENT 'Record content',
  `media_urls` JSON COMMENT 'Media file URLs (images, videos)',
  `tags` VARCHAR(255) COMMENT 'Tags (comma separated)',
  `parent_visible` TINYINT DEFAULT 1 COMMENT 'Parent visible: 0-no, 1-yes',
  `parent_liked` TINYINT DEFAULT 0 COMMENT 'Parent liked: 0-no, 1-yes',
  `parent_comment` VARCHAR(500) COMMENT 'Parent comment',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_tch_growth_record_student` (`student_id`),
  KEY `idx_tch_growth_record_class` (`class_id`, `record_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Growth record table';

7. Meal Management Module
#

7.1 Menu Table (meal_menu)
#

CREATE TABLE `meal_menu` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `menu_date` DATE NOT NULL COMMENT 'Menu date',
  `meal_type` TINYINT NOT NULL COMMENT 'Meal type: 1-breakfast, 2-morning snack, 3-lunch, 4-afternoon snack, 5-dinner',
  `dishes` JSON NOT NULL COMMENT 'Dish list (JSON format)',
  `calories` DECIMAL(8,2) COMMENT 'Total calories (kcal)',
  `protein` DECIMAL(8,2) COMMENT 'Protein (g)',
  `fat` DECIMAL(8,2) COMMENT 'Fat (g)',
  `carbohydrate` DECIMAL(8,2) COMMENT 'Carbohydrate (g)',
  `description` VARCHAR(255) COMMENT 'Description',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_meal_menu` (`menu_date`, `meal_type`),
  KEY `idx_meal_menu_date` (`menu_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Menu table';

7.2 Ingredient Table (meal_ingredient)
#

CREATE TABLE `meal_ingredient` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `ingredient_name` VARCHAR(50) NOT NULL COMMENT 'Ingredient name',
  `ingredient_code` VARCHAR(50) COMMENT 'Ingredient code',
  `category` TINYINT COMMENT 'Category: 1-vegetables, 2-meat, 3-seafood, 4-dairy, 5-grains, 6-seasoning, 7-fruits',
  `unit` VARCHAR(10) COMMENT 'Unit: kg, g, piece, box',
  `unit_price` DECIMAL(8,2) COMMENT 'Unit price',
  `nutrient_info` JSON COMMENT 'Nutrient information',
  `allergy_warning` VARCHAR(255) COMMENT 'Allergy warning',
  `status` TINYINT DEFAULT 1 COMMENT 'Status: 0-disabled, 1-active',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_meal_ingredient_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Ingredient table';

7.3 Procurement Plan Table (meal_procurement_plan)
#

CREATE TABLE `meal_procurement_plan` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `plan_no` VARCHAR(50) COMMENT 'Plan number',
  `plan_date` DATE NOT NULL COMMENT 'Plan date',
  `supplier_id` BIGINT COMMENT 'Supplier ID',
  `total_amount` DECIMAL(10,2) COMMENT 'Total amount',
  `status` TINYINT NOT NULL DEFAULT 0 COMMENT 'Status: 0-pending, 1-approved, 2-procured, 3-received',
  `approver_id` BIGINT COMMENT 'Approver ID',
  `approve_time` DATETIME COMMENT 'Approval time',
  `remark` VARCHAR(255) COMMENT 'Remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_meal_procurement_plan_date` (`plan_date`),
  KEY `idx_meal_procurement_plan_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Procurement plan table';

7.4 Procurement Item Table (meal_procurement_item)
#

CREATE TABLE `meal_procurement_item` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `plan_id` BIGINT NOT NULL COMMENT 'Procurement plan ID',
  `ingredient_id` BIGINT NOT NULL COMMENT 'Ingredient ID',
  `quantity` DECIMAL(10,2) NOT NULL COMMENT 'Quantity',
  `unit` VARCHAR(10) COMMENT 'Unit',
  `unit_price` DECIMAL(8,2) COMMENT 'Unit price',
  `amount` DECIMAL(10,2) COMMENT 'Amount',
  `actual_quantity` DECIMAL(10,2) COMMENT 'Actual received quantity',
  `remark` VARCHAR(255) COMMENT 'Remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_meal_procurement_item_plan` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Procurement item table';

7.5 Supplier Table (meal_supplier)
#

CREATE TABLE `meal_supplier` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `supplier_name` VARCHAR(100) NOT NULL COMMENT 'Supplier name',
  `contact_person` VARCHAR(50) COMMENT 'Contact person',
  `contact_phone` VARCHAR(20) COMMENT 'Contact phone',
  `address` VARCHAR(255) COMMENT 'Address',
  `license_no` VARCHAR(50) COMMENT 'Business license number',
  `license_photo` VARCHAR(255) COMMENT 'Business license photo',
  `supply_category` VARCHAR(255) COMMENT 'Supply category',
  `cooperation_start` DATE COMMENT 'Cooperation start date',
  `status` TINYINT DEFAULT 1 COMMENT 'Status: 0-inactive, 1-active',
  `remark` VARCHAR(255) COMMENT 'Remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_meal_supplier_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Supplier table';

8. Financial Management Module
#

8.1 Fee Item Table (fin_fee_item)
#

CREATE TABLE `fin_fee_item` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `item_name` VARCHAR(50) NOT NULL COMMENT 'Fee item name',
  `item_code` VARCHAR(50) COMMENT 'Item code',
  `item_type` TINYINT NOT NULL COMMENT 'Item type: 1-tuition, 2-meal, 3-uniform, 4-interest class, 5-proxy, 6-other',
  `fee_standard` DECIMAL(10,2) NOT NULL COMMENT 'Fee standard',
  `fee_unit` TINYINT COMMENT 'Fee unit: 1-monthly, 2-semester, 3-per time, 4-yearly',
  `effective_date` DATE COMMENT 'Effective date',
  `expiry_date` DATE COMMENT 'Expiry date',
  `description` VARCHAR(255) COMMENT 'Description',
  `status` TINYINT DEFAULT 1 COMMENT 'Status: 0-disabled, 1-active',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_fin_fee_item_type` (`item_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Fee item table';

8.2 Bill Table (fin_bill)
#

CREATE TABLE `fin_bill` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `bill_no` VARCHAR(50) NOT NULL COMMENT 'Bill number',
  `student_id` BIGINT NOT NULL COMMENT 'Student ID',
  `bill_month` VARCHAR(7) NOT NULL COMMENT 'Bill month (YYYY-MM)',
  `total_amount` DECIMAL(10,2) NOT NULL COMMENT 'Total amount due',
  `paid_amount` DECIMAL(10,2) DEFAULT 0 COMMENT 'Paid amount',
  `discount_amount` DECIMAL(10,2) DEFAULT 0 COMMENT 'Discount amount',
  `deduct_amount` DECIMAL(10,2) DEFAULT 0 COMMENT 'Deduction amount (leave, etc.)',
  `status` TINYINT NOT NULL DEFAULT 0 COMMENT 'Status: 0-unpaid, 1-partial, 2-paid, 3-refunded',
  `due_date` DATE COMMENT 'Due date',
  `paid_time` DATETIME COMMENT 'Payment time',
  `remark` VARCHAR(255) COMMENT 'Remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_fin_bill_no` (`bill_no`),
  KEY `idx_fin_bill_student` (`student_id`),
  KEY `idx_fin_bill_month` (`bill_month`),
  KEY `idx_fin_bill_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Bill table';

8.3 Bill Item Table (fin_bill_item)
#

CREATE TABLE `fin_bill_item` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `bill_id` BIGINT NOT NULL COMMENT 'Bill ID',
  `fee_item_id` BIGINT NOT NULL COMMENT 'Fee item ID',
  `item_name` VARCHAR(50) NOT NULL COMMENT 'Item name',
  `quantity` DECIMAL(10,2) DEFAULT 1 COMMENT 'Quantity',
  `unit_price` DECIMAL(10,2) NOT NULL COMMENT 'Unit price',
  `amount` DECIMAL(10,2) NOT NULL COMMENT 'Amount',
  `deduct_amount` DECIMAL(10,2) DEFAULT 0 COMMENT 'Deduction amount',
  `paid_amount` DECIMAL(10,2) DEFAULT 0 COMMENT 'Paid amount',
  `remark` VARCHAR(255) COMMENT 'Remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_fin_bill_item_bill` (`bill_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Bill item table';

8.4 Payment Record Table (fin_payment)
#

CREATE TABLE `fin_payment` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `payment_no` VARCHAR(50) NOT NULL COMMENT 'Payment transaction number',
  `bill_id` BIGINT NOT NULL COMMENT 'Bill ID',
  `student_id` BIGINT NOT NULL COMMENT 'Student ID',
  `payer_id` BIGINT COMMENT 'Payer ID (parent)',
  `payer_name` VARCHAR(50) COMMENT 'Payer name',
  `amount` DECIMAL(10,2) NOT NULL COMMENT 'Payment amount',
  `payment_method` TINYINT NOT NULL COMMENT 'Payment method: 1-WeChat, 2-Alipay, 3-bank card, 4-cash, 5-transfer',
  `payment_time` DATETIME NOT NULL COMMENT 'Payment time',
  `transaction_id` VARCHAR(100) COMMENT 'Third-party transaction ID',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT 'Status: 0-pending, 1-success, 2-failed, 3-refunded',
  `refund_amount` DECIMAL(10,2) COMMENT 'Refund amount',
  `refund_time` DATETIME COMMENT 'Refund time',
  `remark` VARCHAR(255) COMMENT 'Remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_fin_payment_no` (`payment_no`),
  KEY `idx_fin_payment_bill` (`bill_id`),
  KEY `idx_fin_payment_student` (`student_id`),
  KEY `idx_fin_payment_time` (`payment_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Payment record table';

9. Health Management Module
#

9.1 Health Record Table (health_record)
#

CREATE TABLE `health_record` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `student_id` BIGINT NOT NULL COMMENT 'Student ID',
  `blood_type` VARCHAR(5) COMMENT 'Blood type',
  `height` DECIMAL(5,2) COMMENT 'Height (cm)',
  `weight` DECIMAL(5,2) COMMENT 'Weight (kg)',
  `vision_left` VARCHAR(10) COMMENT 'Left eye vision',
  `vision_right` VARCHAR(10) COMMENT 'Right eye vision',
  `hearing_left` VARCHAR(10) COMMENT 'Left ear hearing',
  `hearing_right` VARCHAR(10) COMMENT 'Right ear hearing',
  `teeth_status` VARCHAR(255) COMMENT 'Teeth condition',
  `allergy_history` TEXT COMMENT 'Allergy history',
  `disease_history` TEXT COMMENT 'Medical history',
  `medication_history` TEXT COMMENT 'Medication history',
  `special_care` TEXT COMMENT 'Special care needs',
  `check_date` DATE COMMENT 'Checkup date',
  `check_hospital` VARCHAR(100) COMMENT 'Checkup hospital',
  `remark` VARCHAR(500) COMMENT 'Remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_health_record_student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Health record table';

9.2 Vaccination Record Table (health_vaccination)
#

CREATE TABLE `health_vaccination` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `student_id` BIGINT NOT NULL COMMENT 'Student ID',
  `vaccine_name` VARCHAR(100) NOT NULL COMMENT 'Vaccine name',
  `vaccine_type` VARCHAR(50) COMMENT 'Vaccine type',
  `dose_no` INT COMMENT 'Dose number',
  `vaccination_date` DATE NOT NULL COMMENT 'Vaccination date',
  `vaccination_org` VARCHAR(100) COMMENT 'Vaccination organization',
  `batch_no` VARCHAR(50) COMMENT 'Vaccine batch number',
  `manufacturer` VARCHAR(100) COMMENT 'Manufacturer',
  `next_date` DATE COMMENT 'Next vaccination date',
  `remark` VARCHAR(255) COMMENT 'Remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_health_vaccination_student` (`student_id`),
  KEY `idx_health_vaccination_date` (`vaccination_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Vaccination record table';

9.3 Morning Check Table (health_morning_check)
#

CREATE TABLE `health_morning_check` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `student_id` BIGINT NOT NULL COMMENT 'Student ID',
  `check_date` DATE NOT NULL COMMENT 'Check date',
  `temperature` DECIMAL(4,2) COMMENT 'Temperature (°C)',
  `spirit_status` TINYINT COMMENT 'Spirit status: 1-good, 2-fair, 3-poor',
  `skin_status` TINYINT COMMENT 'Skin condition: 1-normal, 2-abnormal',
  `hand_foot_status` TINYINT COMMENT 'Hand/foot condition: 1-normal, 2-abnormal',
  `mouth_status` TINYINT COMMENT 'Mouth condition: 1-normal, 2-abnormal',
  `eye_status` TINYINT COMMENT 'Eye condition: 1-normal, 2-abnormal',
  `overall_status` TINYINT NOT NULL COMMENT 'Overall status: 1-normal, 2-observe, 3-abnormal',
  `abnormal_desc` VARCHAR(255) COMMENT 'Abnormal description',
  `handler` VARCHAR(50) COMMENT 'Handler',
  `checker_id` BIGINT COMMENT 'Checker ID',
  `remark` VARCHAR(255) COMMENT 'Remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_health_morning_check` (`student_id`, `check_date`),
  KEY `idx_health_morning_check_date` (`check_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Morning check table';

9.4 Medication Record Table (health_medication)
#

CREATE TABLE `health_medication` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `student_id` BIGINT NOT NULL COMMENT 'Student ID',
  `medicine_name` VARCHAR(100) NOT NULL COMMENT 'Medicine name',
  `dosage` VARCHAR(50) COMMENT 'Dosage',
  `frequency` VARCHAR(50) COMMENT 'Frequency',
  `start_date` DATE NOT NULL COMMENT 'Start date',
  `end_date` DATE COMMENT 'End date',
  `medicine_time` TIME COMMENT 'Medicine time',
  `reason` VARCHAR(255) COMMENT 'Reason',
  `prescription_photo` VARCHAR(255) COMMENT 'Prescription photo',
  `parent_request` TEXT COMMENT 'Parent instructions',
  `status` TINYINT DEFAULT 1 COMMENT 'Status: 1-ongoing, 2-completed, 3-cancelled',
  `nurse_id` BIGINT COMMENT 'Executor ID',
  `execute_time` DATETIME COMMENT 'Execute time',
  `execute_remark` VARCHAR(255) COMMENT 'Execute remark',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_health_medication_student` (`student_id`),
  KEY `idx_health_medication_date` (`start_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Medication record table';

10. Message Notification Module
#

10.1 Notice Table (msg_notice)
#

CREATE TABLE `msg_notice` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `notice_type` TINYINT NOT NULL COMMENT 'Notice type: 1-kindergarten, 2-class, 3-system',
  `title` VARCHAR(100) NOT NULL COMMENT 'Title',
  `content` TEXT NOT NULL COMMENT 'Content',
  `cover_image` VARCHAR(255) COMMENT 'Cover image',
  `attachments` JSON COMMENT 'Attachment list',
  `target_type` TINYINT NOT NULL COMMENT 'Target type: 1-all, 2-specific classes, 3-specific parents',
  `target_ids` JSON COMMENT 'Target ID list',
  `publisher_id` BIGINT NOT NULL COMMENT 'Publisher ID',
  `publish_time` DATETIME COMMENT 'Publish time',
  `status` TINYINT NOT NULL DEFAULT 0 COMMENT 'Status: 0-draft, 1-published, 2-withdrawn',
  `top_flag` TINYINT DEFAULT 0 COMMENT 'Top flag: 0-no, 1-yes',
  `read_count` INT DEFAULT 0 COMMENT 'Read count',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` BIGINT,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_msg_notice_type` (`notice_type`),
  KEY `idx_msg_notice_status` (`status`),
  KEY `idx_msg_notice_publish_time` (`publish_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Notice table';

10.2 Notice Read Record Table (msg_notice_read)
#

CREATE TABLE `msg_notice_read` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `notice_id` BIGINT NOT NULL COMMENT 'Notice ID',
  `user_id` BIGINT NOT NULL COMMENT 'User ID',
  `read_time` DATETIME NOT NULL COMMENT 'Read time',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY `uk_msg_notice_read` (`notice_id`, `user_id`),
  KEY `idx_msg_notice_read_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Notice read record table';

10.3 Message Table (msg_message)
#

CREATE TABLE `msg_message` (
  `id` BIGINT PRIMARY KEY COMMENT 'Primary key ID',
  `conversation_id` VARCHAR(50) COMMENT 'Conversation ID',
  `sender_id` BIGINT NOT NULL COMMENT 'Sender ID',
  `receiver_id` BIGINT NOT NULL COMMENT 'Receiver ID',
  `message_type` TINYINT NOT NULL COMMENT 'Message type: 1-text, 2-image, 3-voice, 4-file',
  `content` TEXT COMMENT 'Message content',
  `media_url` VARCHAR(255) COMMENT 'Media file URL',
  `status` TINYINT DEFAULT 0 COMMENT 'Status: 0-unread, 1-read',
  `read_time` DATETIME COMMENT 'Read time',
  -- Common fields
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted` TINYINT NOT NULL DEFAULT 0,
  KEY `idx_msg_message_conversation` (`conversation_id`),
  KEY `idx_msg_message_sender` (`sender_id`),
  KEY `idx_msg_message_receiver` (`receiver_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Message table';

11. Table Relationship Summary
#

11.1 Core Relationships
#

sys_user ──┬── sys_user_role ──── sys_role ──── sys_role_permission ──── sys_permission
           │
           ├── cls_teacher ──── cls_teacher_class ──── cls_class
           │                                              │
           │                                              ├── stu_student_class ──── stu_student
           │                                              │                              │
           │                                              │                              ├── stu_family_member
           │                                              │                              │
           │                                              │                              ├── att_student_attendance
           │                                              │                              │
           │                                              │                              ├── att_leave_request
           │                                              │                              │
           │                                              │                              ├── att_pickup_record
           │                                              │                              │
           │                                              │                              ├── tch_growth_record
           │                                              │                              │
           │                                              │                              ├── fin_bill ──── fin_bill_item
           │                                              │                              │                │
           │                                              │                              │                └── fin_payment
           │                                              │                              │
           │                                              │                              └── health_record
           │                                              │                                        │
           │                                              │                                        ├── health_vaccination
           │                                              │                                        │
           │                                              │                                        ├── health_morning_check
           │                                              │                                        │
           │                                              │                                        └── health_medication
           │                                              │
           │                                              └── tch_course_schedule ──── tch_course
           │
           └── (Parent user linked to stu_family_member)

11.2 Table Count Summary
#

Module	Table Count	Main Tables
System Management	5	sys_user, sys_role, sys_permission
Student Management	3	stu_student, stu_family_member, stu_student_class
Class Management	3	cls_class, cls_teacher, cls_teacher_class
Attendance Management	4	att_student_attendance, att_leave_request, att_pickup_record
Teaching Management	3	tch_course, tch_course_schedule, tch_growth_record
Meal Management	5	meal_menu, meal_ingredient, meal_procurement_plan
Financial Management	4	fin_fee_item, fin_bill, fin_payment
Health Management	4	health_record, health_vaccination, health_morning_check
Message Notification	3	msg_notice, msg_notice_read, msg_message
Total	34

12. Design Key Points Summary
#

12.1 Data Security
#

Sensitive fields (ID card, password) stored encrypted
Face feature data stored in separate field for security control
Soft delete design supporting data recovery and auditing

12.2 Performance Optimization
#

Reasonable index setup, composite indexes for high-frequency query fields
Large text fields (content, media_urls) use TEXT/JSON types
Statistical fields (current_count, read_count) consider caching

12.3 Extensibility
#

Use JSON fields for dynamic data (dish list, attachment list)
Status fields reserved for extension
Unified common fields for future audit functionality

12.4 Business Integrity
#

Student-class association preserves history for promotion tracking
Bill and payment separated for partial payment and refund support
Complete leave approval workflow with cancellation and rejection

This database design covers core business scenarios of kindergarten management and can be trimmed or extended based on actual requirements. It’s recommended to supplement field constraints and trigger designs according to specific business rules before development.

Kindergarten Management System - This article is part of a series.

§ : This article

1. Database Design Principles#

1.1 Naming Conventions#

1.2 Common Fields#

1.3 Module Prefixes#

2. System Management Module#

2.1 User Table (sys_user)#

2.2 Role Table (sys_role)#

2.3 User-Role Association Table (sys_user_role)#

2.4 Permission Table (sys_permission)#

2.5 Role-Permission Association Table (sys_role_permission)#

3. Student Management Module#

3.1 Student Information Table (stu_student)#

3.2 Family Member Table (stu_family_member)#

3.3 Student-Class Association Table (stu_student_class)#

4. Class Management Module#

4.1 Class Table (cls_class)#

4.2 Teacher Information Table (cls_teacher)#

4.3 Teacher-Class Association Table (cls_teacher_class)#

5. Attendance Management Module#

5.1 Student Attendance Table (att_student_attendance)#

5.2 Leave Request Table (att_leave_request)#

5.3 Pickup Record Table (att_pickup_record)#

5.4 Teacher Attendance Table (att_teacher_attendance)#

6. Teaching Management Module#

6.1 Course Table (tch_course)#

6.2 Course Schedule Table (tch_course_schedule)#

6.3 Growth Record Table (tch_growth_record)#

7. Meal Management Module#

7.1 Menu Table (meal_menu)#

7.2 Ingredient Table (meal_ingredient)#

7.3 Procurement Plan Table (meal_procurement_plan)#

7.4 Procurement Item Table (meal_procurement_item)#

7.5 Supplier Table (meal_supplier)#

8. Financial Management Module#

8.1 Fee Item Table (fin_fee_item)#

8.2 Bill Table (fin_bill)#

8.3 Bill Item Table (fin_bill_item)#

8.4 Payment Record Table (fin_payment)#

9. Health Management Module#

9.1 Health Record Table (health_record)#

9.2 Vaccination Record Table (health_vaccination)#

9.3 Morning Check Table (health_morning_check)#

9.4 Medication Record Table (health_medication)#

10. Message Notification Module#

10.1 Notice Table (msg_notice)#

10.2 Notice Read Record Table (msg_notice_read)#

10.3 Message Table (msg_message)#

11. Table Relationship Summary#

11.1 Core Relationships#

11.2 Table Count Summary#

12. Design Key Points Summary#

12.1 Data Security#

12.2 Performance Optimization#

12.3 Extensibility#

12.4 Business Integrity#

Related articles