79 lines
3.7 KiB
SQL
79 lines
3.7 KiB
SQL
-- ============================================
|
||
-- 团课相关表
|
||
-- ============================================
|
||
|
||
-- 团课课程表
|
||
CREATE TABLE IF NOT EXISTS group_course (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
course_name VARCHAR(100) NOT NULL,
|
||
coach_id BIGINT,
|
||
course_type BIGINT,
|
||
start_time TIMESTAMP NOT NULL,
|
||
end_time TIMESTAMP NOT NULL,
|
||
max_members INTEGER DEFAULT 20,
|
||
current_members INTEGER DEFAULT 0,
|
||
status VARCHAR(1) DEFAULT '0',
|
||
actual_start_time TIMESTAMP,
|
||
actual_end_time TIMESTAMP,
|
||
location VARCHAR(255),
|
||
cover_image VARCHAR(500),
|
||
description TEXT,
|
||
stored_value_amount DECIMAL(10,2) DEFAULT 0,
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- 团课预约记录表
|
||
CREATE TABLE IF NOT EXISTS group_course_booking (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
course_id BIGINT NOT NULL,
|
||
member_id BIGINT NOT NULL,
|
||
member_card_id BIGINT NOT NULL,
|
||
booking_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
status VARCHAR(1) DEFAULT '0',
|
||
cancel_time TIMESTAMP,
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
COMMENT ON TABLE group_course IS '团课课程表';
|
||
COMMENT ON COLUMN group_course.id IS '主键ID';
|
||
COMMENT ON COLUMN group_course.course_name IS '课程名称';
|
||
COMMENT ON COLUMN group_course.coach_id IS '教练ID（关联sys_user）';
|
||
COMMENT ON COLUMN group_course.course_type IS '课程类型（如瑜伽/普拉提/动感单车）';
|
||
COMMENT ON COLUMN group_course.start_time IS '开始时间';
|
||
COMMENT ON COLUMN group_course.end_time IS '结束时间';
|
||
COMMENT ON COLUMN group_course.max_members IS '最大参与人数';
|
||
COMMENT ON COLUMN group_course.current_members IS '当前参与人数';
|
||
COMMENT ON COLUMN group_course.status IS '状态（0正常 1已取消 2已结束 3进行中 4超时）';
|
||
COMMENT ON COLUMN group_course.actual_start_time IS '实际开课时间（教练点击开课时记录）';
|
||
COMMENT ON COLUMN group_course.actual_end_time IS '实际结课时间（教练点击结课时记录）';
|
||
COMMENT ON COLUMN group_course.location IS '上课地点';
|
||
COMMENT ON COLUMN group_course.cover_image IS '封面图URL';
|
||
COMMENT ON COLUMN group_course.description IS '课程描述';
|
||
COMMENT ON COLUMN group_course.stored_value_amount IS '储值卡额度（消耗金额）';
|
||
COMMENT ON COLUMN group_course.create_by IS '创建人';
|
||
COMMENT ON COLUMN group_course.update_by IS '更新人';
|
||
COMMENT ON COLUMN group_course.created_at IS '创建时间';
|
||
COMMENT ON COLUMN group_course.updated_at IS '更新时间';
|
||
COMMENT ON COLUMN group_course.deleted_at IS '删除时间（软删除）';
|
||
|
||
COMMENT ON TABLE group_course_booking IS '团课预约记录表';
|
||
COMMENT ON COLUMN group_course_booking.id IS '主键ID';
|
||
COMMENT ON COLUMN group_course_booking.course_id IS '团课ID';
|
||
COMMENT ON COLUMN group_course_booking.member_id IS '用户ID';
|
||
COMMENT ON COLUMN group_course_booking.member_card_id IS '会员卡ID';
|
||
COMMENT ON COLUMN group_course_booking.booking_time IS '预约时间';
|
||
COMMENT ON COLUMN group_course_booking.status IS '状态（0已预约 1已取消 2已出席 3缺席）';
|
||
COMMENT ON COLUMN group_course_booking.cancel_time IS '取消时间';
|
||
COMMENT ON COLUMN group_course_booking.create_by IS '创建人';
|
||
COMMENT ON COLUMN group_course_booking.update_by IS '更新人';
|
||
COMMENT ON COLUMN group_course_booking.created_at IS '创建时间';
|
||
COMMENT ON COLUMN group_course_booking.updated_at IS '更新时间';
|
||
COMMENT ON COLUMN group_course_booking.deleted_at IS '删除时间（软删除）'; |