Data Structures

12120

Table Overview

General Tables

• _user
• _user_session
• _role
• _page
• _resource
• _group
• _user_group_role
• _user_group_role_page
• _user_group_role_resource
• _constant
• _constant_ui
• _record
• _record_history
• _cache
• _file
• _view01_user

Business Tables

_ui

• UI Construction Plan

CREATE TABLE `_ui` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pageId` varchar(255) DEFAULT NULL COMMENT 'page id; E.g: index',
  `uiActionType` varchar(255) DEFAULT NULL COMMENT 'UI action type, such as: fetchData, postData, changeUi',
  `uiActionId` varchar(255) DEFAULT NULL COMMENT 'action id; E.g: selectXXXByXXX',
  `desc` varchar(255) DEFAULT NULL COMMENT 'description',
  `uiActionConfig` text COMMENT 'UI action data',
  `appDataSchema` text COMMENT 'UI validation data',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'UI construction plan'

article

CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `articleId` bigint(20) DEFAULT NULL COMMENT 'article id, 10000 ++',
  `categoryId` varchar(255) DEFAULT NULL,
  `categoryName` varchar(255) DEFAULT NULL,
  `articleCategoryId` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'category id',
  `courseId` varchar(255) DEFAULT NULL COMMENT 'course id',
  `courseBatchId` varchar(255) DEFAULT NULL,
  `articleGroupName` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'article group name',
  `articleTagList` text CHARACTER SET utf8mb4 COMMENT 'tags; concatenated with ,',
  `articlePublishStatus` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'article status: public, login, draft, deleted',
  `articlePublishTime` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'article publish time',
  `articleTitle` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'title',
  `articleShortName` varchar(255) DEFAULT NULL COMMENT 'short title',
  `articleConfig` text COMMENT 'courseware configuration: exam, duration, etc.',
  `articleCoverImage` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'cover image',
  `articleContent` longtext CHARACTER SET utf8mb4 COMMENT 'content; article content, exam questions/answers, homework questions/answers, news content',
  `articleContentForSeo` longtext CHARACTER SET utf8mb4 COMMENT 'HTML for rendering',
  `articleType` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'article type: article=article/exam=exam/assignment=assignment/material=material/news=news',
  `articleAudioUrl` varchar(1023) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'default audio URL',
  `articleVideoUrl` varchar(1023) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'default video URL',
  `articleCreateTime` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'creation time',
  `articleCreateUserId` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'creator user ID',
  `articleCreateUsername` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'creator username',
  `articleUpdateTime` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'update time',
  `articleUpdateUserId` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'updater user ID',
  `articleUpdateUsername` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'updater username',
  `articlePrice` int(11) DEFAULT '0' COMMENT 'price;',
  `visitCount` int(11) DEFAULT '0' COMMENT 'visit count',
  `operation` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'operation: insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'operator userId',
  `operationByUser` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'operator username',
  `operationAt` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00',
  `auditStatus` varchar(255) DEFAULT 'pending' COMMENT 'audit status; pending=not audited, approve=audited, reject=not approved',
  `auditByUserId` varchar(255) DEFAULT '' COMMENT 'auditor userId;',
  `auditComment` varchar(255) DEFAULT '' COMMENT 'audit comments;',
  `auditAt` varchar(255) DEFAULT '' COMMENT 'audit time;',
  `ownerUserId` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'owner userId',
  `ownerUserName` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'owner',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `article_unique` (`articleId`) USING BTREE,
  KEY `categoryId_index` (`articleCategoryId`) USING BTREE,
  KEY `articlePublishTime` (`articlePublishTime`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8

article_history

CREATE TABLE `article_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `articleId` bigint(20) DEFAULT NULL COMMENT 'article id, 10000 ++',
  `categoryId` varchar(255) DEFAULT NULL COMMENT 'category ID',
  `categoryName` varchar(255) DEFAULT NULL COMMENT 'category name',
  `articleCategoryId` varchar(255) DEFAULT NULL,
  `courseId` varchar(255) DEFAULT NULL COMMENT 'course id',
  `courseBatchId` varchar(255) DEFAULT NULL COMMENT 'course id',
  `articleGroupName` varchar(255) DEFAULT NULL COMMENT 'article group name',
  `articleTagList` varchar(255) DEFAULT NULL COMMENT 'tags; concatenated with ,',
  `articlePublishStatus` varchar(255) DEFAULT 'login' COMMENT 'article status: public, login, draft, deleted',
  `articlePublishTime` varchar(255) DEFAULT NULL COMMENT 'article publish time',
  `articleApproveStatus` varchar(255) DEFAULT NULL,
  `articleTitle` varchar(255) DEFAULT NULL COMMENT 'title',
  `articleShortName` varchar(255) DEFAULT NULL COMMENT 'short title',
  `articleConfig` text,
  `articleCoverImage` varchar(255) DEFAULT NULL COMMENT 'cover image',
  `articleContent` longtext COMMENT 'edited content',
  `articleContentForSeo` longtext COMMENT 'HTML for rendering',
  `articleAssignment` text COMMENT 'article assignments [{ }]',
  `articleAssignmentWithAnswer` text COMMENT 'article assignment answers [{ }]',
  `articleAudioUrl` varchar(255) DEFAULT NULL COMMENT 'default audio URL',
  `articleVideoUrl` varchar(255) DEFAULT NULL COMMENT 'default video URL',
  `articleCreateTime` varchar(255) DEFAULT NULL COMMENT 'creation time',
  `articleCreateUserId` varchar(255) DEFAULT NULL COMMENT 'creator user ID',
  `articleCreateUsername` varchar(255) DEFAULT NULL COMMENT 'creator username',
  `articleUpdateTime` varchar(255) DEFAULT NULL COMMENT 'update time',
  `articleUpdateUserId` varchar(255) DEFAULT NULL COMMENT 'updater user ID',
  `articlePrice` int(11) DEFAULT NULL COMMENT 'article price',
  `articleType` varchar(255) DEFAULT NULL COMMENT 'article type',
  `articleUpdateUsername` varchar(255) DEFAULT NULL COMMENT 'updater username',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation: insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00',
  `articleAssignmentPublishStatus` varchar(255) DEFAULT NULL COMMENT 'article assignment publish status; publish;',
  `ownerUserId` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'owner userId',
  `ownerUserName` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'owner',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8mb4

article_student_response

• Student Article Response Table

CREATE TABLE `article_student_response` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `responseId` varchar(30) DEFAULT NULL COMMENT 'response ID',
  `courseId` varchar(30) DEFAULT NULL COMMENT 'course ID',
  `classId` varchar(50) DEFAULT NULL COMMENT 'class ID',
  `articleTitle` varchar(255) DEFAULT NULL COMMENT 'title',
  `articleId` varchar(30) DEFAULT NULL COMMENT 'article ID',
  `articleType` varchar(255) DEFAULT NULL COMMENT 'article type: article=article/exam=exam/assignment=assignment/news=news',
  `userId` varchar(50) DEFAULT NULL COMMENT 'user ID',
  `fullMark` int(11) DEFAULT NULL COMMENT 'full score',
  `totalMark` int(11) DEFAULT NULL COMMENT 'actual score',
  `responseNote` varchar(255) DEFAULT NULL COMMENT 'note',
  `responseSubmitStatus` varchar(255) DEFAULT NULL COMMENT 'submission status; publish',
  `responseSubmitAt` varchar(255) DEFAULT NULL COMMENT 'submission time',
  `responseContent` text COMMENT 'user submitted content + standard answer',
  `responseRetryNumber` int(11) DEFAULT NULL COMMENT 'number of retries',
  `teacherId` varchar(255) DEFAULT NULL COMMENT 'grading teacher ID',
  `teacherReview` text COMMENT 'grading teacher comments',
  `teacherReviewStatus` varchar(255) DEFAULT '' COMMENT 'grading status; publish',
  `teacherReviewAt` varchar(255) DEFAULT NULL COMMENT 'grading time',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator user ID; userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator;',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00',
  `responsePercent` int(3) DEFAULT '0' COMMENT 'response progress percentage;',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'Student Article Response Table'

attendance

CREATE TABLE `attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `classId` varchar(255)  DEFAULT NULL,
  `courseId` varchar(255)  DEFAULT NULL,
  `userId` varchar(255)  DEFAULT NULL COMMENT 'student user ID;',
  `lessonNumber` int(11) DEFAULT NULL COMMENT 'lesson number;',
  `attendanceDate` varchar(255)  DEFAULT NULL COMMENT 'attendance date;',
  `attendance` varchar(255)  DEFAULT NULL COMMENT 'attendance type; present, absent, late, leave early',
  `articleId` varchar(255)  DEFAULT NULL COMMENT 'article ID',
  `articleTitle` varchar(255)  DEFAULT NULL COMMENT 'article title',
  `markedAt` varchar(255)  DEFAULT NULL COMMENT 'creation time',
  `markedByUser` varchar(255)  DEFAULT NULL COMMENT 'creator',
  `markedByUserId` varchar(255)  DEFAULT NULL COMMENT 'creator ID;',
  `remarks` varchar(255)  DEFAULT NULL COMMENT 'remarks; explanation of absence',
  `operation` varchar(255) CHARACTER SET utf8mb4 DEFAULT '',
  `operationAt` varchar(255) CHARACTER SET utf8mb4 DEFAULT '',
  `operationByUserId` varchar(255) CHARACTER SET utf8mb4 DEFAULT '',
  `operationByUser` varchar(255) CHARACTER SET utf8mb4 DEFAULT '',
  `courseBatchId` varchar(255)  DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `id` (`id`) USING BTREE,
  KEY `classId` (`classId`) USING BTREE,
  KEY `studentId` (`userId`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

course

• Course

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseId` varchar(255) DEFAULT NULL COMMENT 'course id;',
  `categoryId` varchar(255) DEFAULT NULL COMMENT 'course category id;',
  `courseTitle` varchar(255) DEFAULT '' COMMENT 'title;',
  `courseType` varchar(255) DEFAULT '6' COMMENT 'course type; article=graphic topic; audio=audio topic; video=video topic; live=live topic; column=column; other=other topic',
  `courseContent` longtext COMMENT 'content;',
  `courseImage` varchar(255) DEFAULT '' COMMENT 'cover image;',
  `courseTag` varchar(1000) DEFAULT '' COMMENT 'tags;',
  `coursePrice` decimal(8, 2) DEFAULT NULL COMMENT 'amount;',
  `banner` varchar(2000) DEFAULT '' COMMENT 'banner image;',
  `posterImage` varchar(255) DEFAULT '' COMMENT 'poster;',
  `serviceCode` varchar(255) DEFAULT '' COMMENT 'customer service QR code;',
  `freeAritleIdList` varchar(255) DEFAULT NULL COMMENT 'free article ID list;',
  `isDel` varchar(255) DEFAULT 'false' COMMENT 'is deleted;',
  `isFree` varchar(255) DEFAULT 'true' COMMENT 'is free;',
  `freeMemberPrivilegeLevel` varchar(255) DEFAULT NULL COMMENT 'minimum membership level for free;',
  `sort` int(11) DEFAULT '0' COMMENT 'sort;',
  `sales` varchar(255) DEFAULT '0' COMMENT 'actual sales;',
  `userCount` int(11) DEFAULT '0' COMMENT 'number of users;',
  `operation` varchar(255) DEFAULT '' COMMENT 'operation: insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT '' COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT '' COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT '' COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00',
  `auditStatus` varchar(255) DEFAULT 'pending' COMMENT 'audit status',
  `auditByUserId` varchar(255) DEFAULT '' COMMENT 'auditor userId;',
  `auditComment` varchar(255) DEFAULT '' COMMENT 'audit comments;',
  `auditAt` varchar(255) DEFAULT '' COMMENT 'audit time;',
  `ownerUserId` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'owner userId',
  `ownerUserName` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'owner',
  `helperUserIdList` varchar(255) DEFAULT NULL COMMENT 'assistant user ID list;',
  `helperUserNameList` varchar(255) DEFAULT NULL COMMENT 'assistant username list;',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8mb4 COMMENT = 'Course'

course_article

• Course Article Table

CREATE TABLE `course_article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseId` varchar(255) DEFAULT NULL COMMENT 'course id;',
  `articleId` varchar(255) DEFAULT NULL COMMENT 'article id;',
  `albumId` varchar(255) DEFAULT NULL COMMENT 'textbook id;',
  `lessonNumber` int(11) DEFAULT NULL COMMENT 'lesson number;',
  `albumTitle` varchar(255) DEFAULT NULL COMMENT 'textbook title;',
  `articleType` varchar(255) DEFAULT NULL COMMENT 'article type: article=article/exam=exam/assignment=assignment/news=news',
  `articleTagList` text COMMENT 'tags; concatenated with ,',
  `articlePublishStatus` varchar(255) DEFAULT '' COMMENT 'article status; public, login, draft, deleted',
  `articlePublishTime` varchar(255) DEFAULT NULL COMMENT 'article publish time;',
  `articleTitle` varchar(255) DEFAULT NULL COMMENT 'title;',
  `articleDesignStatus` varchar(255) DEFAULT 'firstDraft' COMMENT 'draft status; firstDraft | secondDraft | thirdDraft',
  `articleCoverImage` varchar(255) DEFAULT NULL COMMENT 'cover;',
  `isFree` varchar(255) DEFAULT 'true' COMMENT 'is free; true=yes, false=no',
  `price` int(11) DEFAULT NULL COMMENT 'price;',
  `visitCount` int(11) DEFAULT '0' COMMENT 'visit count; download count of materials',
  `questionCount` int(11) DEFAULT '0' COMMENT 'number of questions;',
  `responseCount` int(11) DEFAULT '0' COMMENT 'response count;',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator user ID; userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator;',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'Course Article Table'

course_category

• Course Category List

CREATE TABLE `course_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `categoryId` varchar(255) NOT NULL COMMENT 'category id;',
  `categoryName` varchar(255) NOT NULL DEFAULT '' COMMENT 'category name;',
  `icon` varchar(255) DEFAULT '' COMMENT 'category icon;',
  `sort` int(11) NOT NULL DEFAULT '0' COMMENT 'sort;',
  `isShown` varchar(255) DEFAULT 'true' COMMENT 'is displayed; true=shown, false=hidden',
  `isDel` varchar(255) DEFAULT 'false' COMMENT 'is deleted; false=not deleted, true=deleted',
  `operation` varchar(255) DEFAULT '' COMMENT 'operation: insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT '' COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT '' COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT '' COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 129 DEFAULT CHARSET = utf8mb4 COMMENT = 'Course Category List'

course_gift

• Course Gift Table

CREATE TABLE `course_gift` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `giftId` varchar(255) NOT NULL DEFAULT '0' COMMENT 'gift ID, git_random number',
  `courseId` varchar(255) NOT NULL DEFAULT '' COMMENT 'course ID',
  `courseName` varchar(255) DEFAULT NULL COMMENT 'course name',
  `courseImage` varchar(255) DEFAULT NULL COMMENT 'cover image;',
  `coursePrice` decimal(8, 2) DEFAULT NULL COMMENT 'amount;',
  `userId` varchar(255) NOT NULL DEFAULT '' COMMENT 'user ID',
  `userName` varchar(255) DEFAULT '0' COMMENT 'username',
  `userAvatar` varchar(255) DEFAULT NULL COMMENT 'user avatar',
  `receiveUserId` varchar(255) DEFAULT '' COMMENT 'recipient ID',
  `receiveUserName` varchar(255) DEFAULT '' COMMENT 'recipient',
  `receiveTime` varchar(255) DEFAULT '' COMMENT 'receive time',
  `receiveStatus` varchar(255) NOT NULL DEFAULT 'waitReceive' COMMENT 'receive status; waitReceive=not received; received=received;',
  `operation` varchar(255) CHARACTER SET utf8mb4  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'Course Gift Table'

course_user

• Course User Table

CREATE TABLE `course_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseId` varchar(255) NOT NULL DEFAULT '' COMMENT 'course id; ',
  `courseTitle` varchar(255) DEFAULT '' COMMENT 'title;',
  `courseType` varchar(255) DEFAULT 'article' COMMENT 'course type; article=graphic, audio=audio, video=video course, live=live course, column=column, other=other course',
  `courseTag` varchar(1000) DEFAULT '' COMMENT 'tags;',
  `courseImage` varchar(255) DEFAULT '' COMMENT 'cover image;',
  `userId` varchar(255) NOT NULL DEFAULT '' COMMENT 'user id; ',
  `userName` varchar(255) DEFAULT '' COMMENT 'username; ',
  `userAvatar` varchar(255) DEFAULT '' COMMENT 'user avatar; ',
  `classId` varchar(255) DEFAULT NULL COMMENT 'assigned class id; ',
  `orderId` varchar(255) DEFAULT NULL COMMENT 'order ID',
  `isDel` varchar(255) NOT NULL DEFAULT 'false' COMMENT 'is deleted; true=deleted, false=not deleted',
  `isPay` varchar(255) DEFAULT NULL COMMENT 'is paid',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'Course User Table'

home_recommend

• Home Recommendations

CREATE TABLE `home_recommend` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT '' COMMENT 'title;',
  `type` varchar(255) DEFAULT '' COMMENT 'type; banner=carousel, navIcon=navigation icon, news=news, activity=activity, ad=advertisement, live=live, course=course, link=custom link, goods=goods, hotLive=hot live, teacher=teacher, groupBooking=group booking, menu=menu, material=material',
  `link` varchar(255) DEFAULT '' COMMENT 'custom link path;',
  `icon` varchar(255) DEFAULT '' COMMENT 'icon;',
  `image` varchar(255) DEFAULT '' COMMENT 'cover image;',
  `contentList` text COMMENT 'content list JSON;',
  `contentListOfDemo` text COMMENT 'content list JSON (example);',
  `contentDisplay` varchar(255) DEFAULT '' COMMENT 'content display type; big=large image, grid=grid, small=small image, img=pure image, fixed=fixed',
  `sort` int(11) DEFAULT '0' COMMENT 'sort;',
  `isShown` varchar(255) DEFAULT 'true' COMMENT 'is displayed; true=yes, false=no',
  `isBuildin` varchar(255) DEFAULT 'false' COMMENT 'is built-in; true=yes, false=no',
  `operation` varchar(255) NOT NULL DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `rowStatus` varchar(255) DEFAULT 'true' COMMENT 'data status: true==normal, false==recycle bin',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`, `operation`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8mb4 COMMENT = 'Home Recommendations'

order

• Order Table

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orderId` varchar(255) NOT NULL DEFAULT '' COMMENT 'order number;',
  `orderType` varchar(255) NOT NULL DEFAULT 'course' COMMENT 'order type; course=course order, product=product order, exam=exam order, material=material, member=membership order',
  `orderStatus` varchar(255) NOT NULL DEFAULT 'waitpay' COMMENT 'order status; waitpay=pending payment, paid=paid, waitSend=pending shipment, waitReceive=pending receipt, waitComment=pending review, commented=reviewed, refunded=refunded, cancel=canceled',
  `userName` varchar(255) NOT NULL DEFAULT '' COMMENT 'user name',
  `userId` varchar(255) NOT NULL DEFAULT '' COMMENT 'user id',
  `userPhone` varchar(255) NOT NULL DEFAULT '' COMMENT 'user phone',
  `userAddress` varchar(255) NOT NULL DEFAULT '' COMMENT 'detailed address',
  `courseId` varchar(255) DEFAULT NULL COMMENT 'purchased course ID',
  `memberId` varchar(255) DEFAULT NULL COMMENT 'purchased membership type ID',
  `articleId` varchar(255) DEFAULT NULL COMMENT 'purchased article id; exam and material',
  `productId` varchar(255) DEFAULT NULL COMMENT 'product id;',
  `totalNum` int(11) NOT NULL DEFAULT '1' COMMENT 'total number of items in the order;',
  `totalPrice` decimal(14, 2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'total order price;',
  `totalPostage` decimal(14, 2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'postage;',
  `payPrice` decimal(14, 2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'actual payment amount;',
  `payPostage` decimal(14, 2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'paid postage',
  `payType` varchar(255) NOT NULL DEFAULT 'wechat' COMMENT 'payment method; wechat=WeChat Pay; alipay=Alipay; offline=offline payment',
  `payTradeNo` varchar(255) NOT NULL DEFAULT '' COMMENT 'WeChat/Alipay transaction order number',
  `payTime` varchar(255) DEFAULT NULL COMMENT 'payment time;',
  `merchantId` varchar(255) DEFAULT NULL COMMENT 'payment merchant ID',
  `payStatus` varchar(255) NOT NULL DEFAULT 'unpaid' COMMENT 'payment status; unpaid=not paid; paid=paid; refunding=refund in progress; refunded=refunded; cancel=canceled; received=received',
  `deductionPrice` decimal(14, 2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'deduction amount',
  `couponId` varchar(255) DEFAULT NULL COMMENT 'coupon id',
  `couponPrice` decimal(14, 2) unsigned DEFAULT '0.00' COMMENT 'coupon amount',
  `deliveryName` varchar(255) DEFAULT NULL COMMENT 'courier company',
  `deliveryType` varchar(255) DEFAULT NULL COMMENT 'logistics type',
  `deliveryNumber` varchar(64) DEFAULT NULL COMMENT 'courier tracking number',
  `refundStatus` varchar(255) NOT NULL DEFAULT 'unrefund' COMMENT 'refund status: unrefund=not refunded, applying=applying, refunded=refunded',
  `refundReason` varchar(255) DEFAULT NULL COMMENT 'refund reason',
  `refundReasonPicList` text COMMENT 'refund reason image list',
  `refundRejectReason` varchar(255) DEFAULT NULL COMMENT 'refund rejection reason;',
  `refundApplyTime` varchar(255) DEFAULT NULL COMMENT 'refund application time',
  `refundTime` varchar(255) DEFAULT NULL COMMENT 'refund time',
  `refundPrice` decimal(14, 2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'refund amount',
  `mark` varchar(512) DEFAULT NULL COMMENT 'remarks',
  `isDel` varchar(255) NOT NULL DEFAULT 'false' COMMENT 'is deleted; true=deleted; false=not deleted;',
  `isGift` varchar(255) NOT NULL DEFAULT 'false' COMMENT 'is gift order;',
  `remark` varchar(512) DEFAULT NULL COMMENT 'admin remarks',
  `spreadUserId` varchar(255) DEFAULT NULL COMMENT 'referrer id',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `orderId` (`orderId`) USING BTREE,
  KEY `userId` (`userId`) USING BTREE,
  KEY `orderType` (`orderType`) USING BTREE,
  KEY `orderStatus` (`orderStatus`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8mb4 COMMENT = 'Order Table'

store_category

• Product Category Table

CREATE TABLE `store_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `categoryId` varchar(255) NOT NULL DEFAULT '' COMMENT 'category ID;',
  `categoryName` varchar(255) NOT NULL DEFAULT '' COMMENT 'category name;',
  `sort` int(11) NOT NULL DEFAULT '0' COMMENT 'sort;',
  `icon` varchar(255) NOT NULL DEFAULT '' COMMENT 'icon;',
  `isDel` varchar(255) NOT NULL DEFAULT 'false' COMMENT 'is deleted; false=not deleted, true=deleted',
  `isShown` varchar(255) NOT NULL DEFAULT 'false' COMMENT 'is recommended; false=not recommended, true=recommended',
  `operation` varchar(255) DEFAULT NULL,
  `operationByUserId` varchar(255) DEFAULT NULL,
  `operationByUser` varchar(255) DEFAULT NULL,
  `operationAt` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8mb4 COMMENT = 'Product Category Table'

store_product

• Product Table

CREATE TABLE `store_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `productId` varchar(255) NOT NULL DEFAULT '' COMMENT 'product ID;',
  `productName` varchar(255) NOT NULL DEFAULT '' COMMENT 'product name;',
  `productDesc` varchar(255) DEFAULT '' COMMENT 'product description;',
  `productImage` varchar(255) DEFAULT '' COMMENT 'product image;',
  `banner` varchar(255) DEFAULT '' COMMENT 'carousel image;',
  `keyword` varchar(255) DEFAULT '' COMMENT 'keywords;',
  `categoryId` varchar(255) DEFAULT '' COMMENT 'category id;',
  `price` decimal(14, 2) unsigned DEFAULT NULL COMMENT 'product price;',
  `marketPrice` decimal(14, 2) unsigned DEFAULT NULL COMMENT 'market price (strikethrough price);',
  `unitName` varchar(255) DEFAULT '' COMMENT 'unit name;',
  `sort` int(11) DEFAULT '0' COMMENT 'sort;',
  `sales` int(11) unsigned DEFAULT '0' COMMENT 'sales volume;',
  `salesVitual` int(11) DEFAULT '0' COMMENT 'virtual sales;',
  `stock` int(11) unsigned DEFAULT '0' COMMENT 'stock;',
  `isRecommend` varchar(255) DEFAULT 'false' COMMENT 'is recommended; false=not recommended, true=recommended',
  `isShown` varchar(255) DEFAULT 'false' COMMENT 'status; false=not listed, true=listed;',
  `isDel` varchar(255) DEFAULT 'false' COMMENT 'is deleted; false=not deleted, true=deleted',
  `cost` decimal(14, 2) unsigned DEFAULT NULL COMMENT 'cost price',
  `browse` int(11) DEFAULT '0' COMMENT 'view count;',
  `productContent` text COMMENT 'product details;',
  `operation` varchar(255) DEFAULT NULL,
  `operationByUserId` varchar(255) DEFAULT NULL,
  `operationByUser` varchar(255) DEFAULT NULL,
  `operationAt` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8 COMMENT = 'Product Table'

student_learning_record

CREATE TABLE `student_learning_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `courseMemberId` varchar(255)  DEFAULT NULL COMMENT 'learning record number',
  `courseBatchId` varchar(255)  DEFAULT NULL COMMENT 'current course ID',
  `courseName` varchar(255)  DEFAULT NULL COMMENT 'course name',
  `courseId` varchar(255)  DEFAULT NULL COMMENT 'belonging course series',
  `classId` varchar(255)  DEFAULT NULL COMMENT 'class number',
  `memberId` varchar(255)  DEFAULT NULL COMMENT 'employee number',
  `netName` varchar(255)  DEFAULT NULL COMMENT 'name',
  `loginId` varchar(255)  DEFAULT NULL COMMENT 'user ID',
  `district` varchar(255)  DEFAULT NULL COMMENT 'branch ID',
  `orgId` varchar(255)  DEFAULT NULL COMMENT 'administrative organization ID',
  `orgName` varchar(255)  DEFAULT NULL COMMENT 'administrative organization name',
  `orgFullName` varchar(255)  DEFAULT NULL COMMENT 'full name of administrative organization',
  `roleId` varchar(255)  DEFAULT NULL COMMENT 'organization role ID',
  `stage` varchar(50)  DEFAULT NULL COMMENT 'position stage',
  `title` varchar(50)  DEFAULT NULL COMMENT 'position',
  `position` varchar(50)  DEFAULT NULL COMMENT 'position',
  `memberRole` varchar(50)  DEFAULT NULL COMMENT 'student role in the course',
  `result` varchar(50)  DEFAULT NULL COMMENT 'graduation score',
  `note` text  COMMENT 'note',
  `comment` text  COMMENT 'stage comments',
  `teacherRecommendation` varchar(255)  DEFAULT NULL COMMENT 'recommendation: graduate, fail',
  `courseCreditEarned` varchar(50)  DEFAULT NULL COMMENT 'credits earned',
  `examScoreStat` varchar(255)  DEFAULT NULL COMMENT 'regular score statistics',
  `examScoreDetail` text  COMMENT 'detailed scores for each lesson',
  `overallComment` varchar(1023)  DEFAULT NULL COMMENT 'overall comments',
  `commentDetail` text  COMMENT 'comment details',
  `attendanceStat` varchar(1023)  DEFAULT NULL COMMENT 'attendance statistics',
  `attendanceDetail` text  COMMENT 'attendance details',
  `assignmentStat` varchar(1023)  DEFAULT NULL COMMENT 'assignment statistics',
  `assignmentDetail` text  COMMENT 'assignment detail report',
  `noteStat` varchar(1023)  DEFAULT NULL COMMENT 'note statistics',
  `noteDetail` text  COMMENT 'note detail report',
  `totalLearningHour` varchar(255)  DEFAULT NULL COMMENT 'total learning duration',
  `memberCourseStatus` varchar(255)  DEFAULT NULL COMMENT 'course learning status',
  `nominatedByMemberId` varchar(255)  DEFAULT NULL COMMENT 'referrer employee number',
  `nominatedByUserId` varchar(255)  DEFAULT NULL COMMENT 'referrer username',
  `nominatedByUser` varchar(255)  DEFAULT NULL COMMENT 'referrer',
  `dateStarted` varchar(255)  DEFAULT NULL COMMENT 'learning start time',
  `dateEnded` varchar(255)  DEFAULT NULL COMMENT 'learning end time',
  `operation` varchar(255)  DEFAULT NULL COMMENT 'operation',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  `operationByUserId` varchar(50)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(50)  DEFAULT NULL COMMENT 'operator username (display)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

user_address

• User Address Table

CREATE TABLE `user_address` (
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `addressId` varchar(255) NOT NULL DEFAULT '' COMMENT 'user address ID;',
  `userId` varchar(255) NOT NULL DEFAULT '0' COMMENT 'user ID;',
  `realName` varchar(255) NOT NULL DEFAULT '' COMMENT 'recipient name;',
  `phone` varchar(255) NOT NULL DEFAULT '' COMMENT 'recipient phone;',
  `province` varchar(255) NOT NULL DEFAULT '' COMMENT 'recipient province;',
  `city` varchar(255) NOT NULL DEFAULT '' COMMENT 'recipient city;',
  `district` varchar(255) NOT NULL DEFAULT '' COMMENT 'recipient district;',
  `detail` varchar(255) NOT NULL DEFAULT '' COMMENT 'recipient detailed address;',
  `postCode` varchar(255) DEFAULT '0' COMMENT 'postal code;',
  `longitude` varchar(255) DEFAULT '0' COMMENT 'longitude;',
  `latitude` varchar(255) DEFAULT '0' COMMENT 'latitude;',
  `isDefault` varchar(255) NOT NULL DEFAULT 'false' COMMENT 'is default; false=no, true=yes',
  `operation` varchar(255) CHARACTER SET utf8mb4  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  `districtCode` varchar(255) DEFAULT NULL COMMENT 'district code;',
  `cityCode` varchar(255) DEFAULT NULL COMMENT 'city code;',
  `provinceCode` varchar(255) DEFAULT NULL COMMENT 'province code;',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8 COMMENT = 'User Address Table'

user_comment

• Product Comment Table

CREATE TABLE `user_comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `commentId` varchar(255) NOT NULL DEFAULT '' COMMENT 'comment ID;',
  `commentType` varchar(255) NOT NULL DEFAULT 'course' COMMENT 'comment type; course=course, product=product',
  `courseId` varchar(255) DEFAULT '' COMMENT 'course ID;',
  `courseName` varchar(255) DEFAULT NULL COMMENT 'course name;',
  `productId` varchar(255) DEFAULT '' COMMENT 'product ID;',
  `productName` varchar(255) DEFAULT NULL COMMENT 'product name;',
  `userId` varchar(255) NOT NULL DEFAULT '' COMMENT 'user ID;',
  `orderId` varchar(255) NOT NULL DEFAULT '' COMMENT 'order ID;',
  `nickname` varchar(255) DEFAULT NULL COMMENT 'user nickname;',
  `avatar` varchar(255) DEFAULT NULL COMMENT 'user avatar;',
  `comment` varchar(255) DEFAULT NULL COMMENT 'comment content;',
  `star` int(11) DEFAULT NULL COMMENT 'rating;',
  `pics` varchar(255) DEFAULT NULL COMMENT 'comment images;',
  `replyContent` varchar(255) DEFAULT NULL COMMENT 'reply content;',
  `replyTime` varchar(255) DEFAULT NULL COMMENT 'reply time;',
  `replyUserId` varchar(255) DEFAULT NULL COMMENT 'reply personnel ID;',
  `replyUserName` varchar(255) DEFAULT NULL COMMENT 'reply personnel name;',
  `isReply` varchar(255) DEFAULT NULL COMMENT 'is replied; true=yes, false=no',
  `isDel` varchar(255) DEFAULT NULL COMMENT 'is deleted; true=yes, false=no',
  `isShown` varchar(255) DEFAULT NULL COMMENT 'is displayed; true=yes, false=no',
  `isVirtual` varchar(255) DEFAULT 'false' COMMENT 'is virtual comment;',
  `operation` varchar(255) CHARACTER SET utf8mb4  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT = 'Product Comment Table'

user_learing_record

• User Learning Record

CREATE TABLE `user_learing_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseId` varchar(11) DEFAULT NULL COMMENT 'course id',
  `articleId` varchar(11) DEFAULT NULL COMMENT 'article id',
  `learingType` varchar(255) DEFAULT NULL COMMENT 'learning record type; course, article, exam, assignment',
  `operation` varchar(255) CHARACTER SET utf8mb4  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',