Moodle系统数据库设计项目说明书

Moodle系统数据库设计项目说明书DOCPROPERTY内容MOODLE数据库设计说明书编写：白斌审核：批准：修改说明版本号起止日期摘要作者目录1．引言 7 7 7 7 82．数据库设计说明 8数据库环境说明 8数据库的命名规则 8其他说明 83.结构设计 10 10 11 114.运用设计 11adodb_logsql 11mdl_assignment 11mdl_assignmenmdl_submissions 12mdl_backup_config 13mdl_backup_courses 13mdl_backup_files 13mdl_backup_ids 13mdl_backup_log 14mdl_block 14mdl_block_instance 14mdl_block_pinned 15mdl_block_rss_client 15mdl_blog_tag_instance 15mdl_cache_filters 16mdl_cache_text 16mdl_capabilities 16mdl_chat 16mdl_chat_messages 17mdl_chat_users 17mdl_choice 17mdl_choice_answers 18mdl_choice_options 18mdl_config 18mdl_config_plugins 19mdl_context 19mdl_context_rel 19mdl_course 19mdl_course_allowed_modules 21mdl_course_categories 21mdl_course_display 21mdl_course_meta 21mdl_course_modules 22mdl_course_request 22mdl_course_sections 22mdl_data 23mdl_data_comments 23mdl_data_content 24mdl_data_fields 24mdl_data_ratings 25mdl_data_records 25mdl_enrol_authorize 25mdl_enrol_authorize_refunds 26mdl_enrol_paypal 26mdl_event 27mdl_forum 27mdl_forum_discussions 28mdl_forum_posts 28mdl_forum_queue 29mdl_forum_ratings 29mdl_forum_read 29mdl_forum_subscriptions 30mdl_forum_track_prefs 30mdl_glossary 30mdl_glossary_alias 31mdl_glossary_categories 31mdl_glossary_comments 31mdl_glossary_entries 31mdl_glossary_entries_categories 32mdl_glossary_formats 32mdl_glossary_ratings 33mdl_grade_category 33mdl_grade_exceptions 33mdl_grade_item 33mdl_grade_letter 34mdl_grade_preferences 34mdl_groups 34mdl_groups_courses_groupings 35mdl_groups_courses_groups 35mdl_groups_groupings 35mdl_groups_groupings_groups 36mdl_groups_members 36mdl_hotpot 36mdl_hotpot_attempts 37mdl_hotpot_details 37mdl_hotpot_questions 37mdl_hotpot_responses 38mdl_hotpot_strings 38mdl_journal 38mdl_journal_entries 39mdl_label 39mdl_lams 39mdl_lesson 40mdl_lesson_answers 41mdl_lesson_attempts 41mdl_lesson_branch 41mdl_lesson_default 42mdl_lesson_grades 43mdl_lesson_high_scores 43mdl_lesson_pages 43mdl_lesson_timer 44mdl_log 44mdl_log_display 44mdl_message 45mdl_message_contacts 45mdl_message_read 45mdl_mnet_enrol_assignments 45mdl_mnet_enrol_course 46mdl_mnet_host 46mdl_mnet_host2service 47mdl_mnet_log 47mdl_mnet_rpc 47mdl_mnet_service 48mdl_mnet_service2rpc 48mdl_mnet_session 48mdl_mnet_sso_access_control 49mdl_modules 49mdl_post 49mdl_question 50mdl_question_answers 50mdl_question_attempts 51mdl_question_calculated 51mdl_question_categories 51mdl_question_datasets 51mdl_question_dataset_definitions 52mdl_question_dataset_items 52mdl_question_match 52mdl_question_match_sub 52mdl_question_multianswer 53mdl_question_multichoice 53mdl_question_numerical 53mdl_question_numerical_units 53mdl_question_randomsamatch 54mdl_question_rqp 54mdl_question_rqp_servers 54mdl_question_rqp_states 55mdl_question_rqp_types 55mdl_question_sessions 55mdl_question_shortanswer 55mdl_question_states 56mdl_question_truefalse 56mdl_quiz 56mdl_quiz_attempts 57mdl_quiz_feedback 57mdl_quiz_grades 58mdl_quiz_question_instances 58mdl_quiz_question_versions 58mdl_resource 58mdl_role 59mdl_role_allow_assign 59mdl_role_allow_override 59mdl_role_assignments 60mdl_role_capabilities 60mdl_role_names 60mdl_scale 61mdl_scorm 61mdl_scorm_scoes 62mdl_scorm_scoes_data 62mdl_scorm_scoes_track 62mdl_scorm_seq_mapinfo 63mdl_scorm_seq_objective 63mdl_scorm_seq_rolluprule 63mdl_scorm_seq_rolluprulecond 64mdl_scorm_seq_rulecond 64mdl_scorm_seq_ruleconds 64mdl_sessions2 64mdl_stats_daily 65mdl_stats_monthly 65mdl_stats_user_daily 65mdl_stats_user_monthly 66mdl_stats_user_weekly 66mdl_stats_weekly 66mdl_survey 67mdl_survey_analysis 67mdl_survey_answers 67mdl_survey_questions 68mdl_tags 68mdl_timezone 68mdl_user 69mdl_user_info_category 70mdl_user_info_data 70mdl_user_info_field 70mdl_user_lastaccess 71mdl_user_preferences 71mdl_wiki 71mdl_wiki_entries 72mdl_wiki_locks 72mdl_wiki_pages 72mdl_workshop 73mdl_workshop_assessments 74mdl_workshop_comments 74mdl_workshop_elements 75mdl_workshop_grades 75mdl_workshop_rubrics 75mdl_workshop_stockcomments 75mdl_workshop_submissions 765.安全保密设计 766.优化 767.数据库管理与维护说明 76控制文件的备份 77检查数据库文件的状态 771．引言数据库设计是软件系统设计极为重要的一部分。数据模型设计和数据结构设计是软件开发的基础。本文档着重描述MOODLE平台增补系统的数据库系统设计，旨在描述符合系统业务需求数据库系统设计。系统设计人员、开发人员和客户通过阅读本文了解MOODLE平台增补系统的数据关系、数据字典和数据库对象(表、视图、存储过程等)。本文包括了MOODLE储备管理系统的数据结构设计和数据字典设计，重点是描述清楚每个使用的实体数据项。详细规定数据项的使用属性。本文的读者是MOODLE平台增补系统的系统设计人员、开发人员和客户。参考文献无缩写、术语解释2．数据库设计说明数据库环境说明。数据库有表和序列构成。表存储了应用系统中的数据，序列用于生成表中行的唯一记录编号。数据库的命名规则1．数据库表名以t开头加_加上具体实体名，如果有实体操作分类，即mdl_实体名_类型，例如mdl_project_stat，表示此表是基于project的基础上统计表。2．数据表命名必须遵循下划线分割的原则，所有表明以"mdl_"开头，视图以"v_"开头，序列用"sequence_"开头等。其他说明数据库操作工具使用sqlyogent，其主画面如下：3.结构设计本数据库为独立数据库结构，未升级进行服务器及数据集群配置。4.运用设计 为了更好地对数据进行查询统计，使用户可以将新的字段或数据表加进系统中，并及时的收集、整理数据，本系统建立了数据字典，用来对数据进行管理。数据字典定义了本系统操作的所有数据库以及各数据库中的表、字段等。系统管理程序完成数据字典的维护功能。各子系统数据字典说明如下所述。adodb_logsql描述：sql执行记录表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02created创建时间datetime3sql0SQL语句varchar2504sql1执行内容text655355params参数text655356tracer执行记录text655357timer执行时间decimal16,60.000000mdl_assignment描述：作业资料表序号字段名称字段描述字段类型长度允许空缺省值1idIDbigint19,02course课程IDbigint19,03name作业名称varchar2554description作业描述text655355format格式smallint5,06assignmenttype作业类型varchar507resubmit是否允许学生删除作业tinyint3,08preventlate是否禁止迟交tinyint3,09emailteachers是否使用E-mail提醒教师tinyint3,010var1允许上传文件的最大数目bigint19,011var2是否允许备注bigint19,012var3在作业开始提交前隐藏作业说明bigint19,013var4是否发送评分请求bigint19,014var5bigint19,015maxbytes上传文件大小限制bigint19,010000016timedue截止时间bigint19,017timeavailable开始时间bigint19,018grade成绩bigint19,019timemodified修改时间bigint19,0mdl_assignmenmdl_submissions描述：提交的作业序号字段名称字段描述字段类型长度允许空缺省值1idIDbigint19,02assignment作业IDbigint19,03userid用户IDbigint19,04timecreated作业创建时间bigint19,05timemodified作业提交时间bigint19,06numfiles上传文件个数，0好像是允许多个bigint19,07data1作业信息text655358data2作业是否提交的相关信息text655359grade成绩bigint19,010submissioncomment作业评论text6553511format格式smallint5,012teacher老师IDbigint19,013timemarked打分时间bigint19,014mailed是否发送E-mail通知作业回复情况tinyint3,0mdl_backup_config描述：配置备份表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02name备份名称varchar2553value备份值varchar255mdl_backup_courses描述：课程备份表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02courseid课程IDbigint19,03laststarttime最近开始时间bigint19,04lastendtime最后结束时间bigint19,05laststatus最后的状态varchar16nextstarttime下一个开始时间bigint19,0mdl_backup_files描述：文件备份表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02backup_code备份代码bigint19,03file_type文件类型varchar104path备份路径varchar2555old_id原文件IDbigint19,06new_id新的文件IDbigint19,0mdl_backup_ids描述：标识备份表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02backup_code备份代码bigint19,03table_name表名varchar304old_id原IDbigint19,05new_id新的IDbigint19,06info备份信息mediumtext16777215mdl_backup_log描述：日志备份表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02courseid课程IDbigint19,03time创建时间bigint19,04laststarttime最后开始时间bigint19,05info备份信息说明varchar255mdl_block描述：版块表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02name动作名称varchar403version版本bigint19,04cron是否计划任务bigint19,05lastcron最近一次执行bigint19,06visible是否可见tinyint3,017multiple多个tinyint3,0mdl_block_instance描述：版块实例表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02blockid动作IDbigint19,03pageid页面IDbigint19,04pagetype页码类型varchar205position定位varchar106weight加权smallint5,07visible是否可见tinyint3,08configdata配置数据内容text65535mdl_block_pinned描述：固定版块表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02blockid动作IDbigint19,03pagetype页码类型varchar204position位置varchar105weight加权smallint5,06visible是否可见tinyint3,07configdata配置数据text65535mdl_block_rss_client描述：版块RSS客户端表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02userid用户IDbigint19,03title标题text655354preferredtitle首先标题varchar645description描述text655356shared是否分享tinyint3,07url分享地址varchar255mdl_blog_tag_instance描述：版块标签实例表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02entryid条目IDbigint19,03tagid标签IDbigint19,04groupid群组IDbigint19,05courseid课程IDbigint19,06userid用户IDbigint19,07timemodified编辑时间bigint19,0mdl_cache_filters描述：缓存过滤表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02filter过滤器名varchar323version版本bigint19,04md5keyMD5加密密钥varchar325rawtext原内容text655356timemodified编辑时间bigint19,0mdl_cache_text描述：缓存文本表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02md5keyMD5加密密钥varchar323formattedtext格式化后的内容longtext42949672954timemodified编辑时间bigint19,0mdl_capabilities描述：能力表 序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02name名称varchar2553captype能力类型varchar504contextlevel权限级别bigint19,05component组件varchar1006riskbitmaskbigint19,0mdl_chat描述：聊天室序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02course课程IDbigint19,03name聊天室名varchar2554intro简介text655355keepdays保持天数bigint19,06studentlogs每个人都可以查看过去的会话smallint5,07chattime聊天时间bigint19,08schedule附表smallint5,09timemodified编辑时间bigint19,0mdl_chat_messages描述：聊天信息表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02chatid聊天对话IDbigint19,03userid用户IDbigint19,04groupid群组IDbigint19,05system系统标志位tinyint3,06message信息内容text655357timestamp编辑时间bigint19,0mdl_chat_users描述：聊天用户表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02chatid聊天对话IDbigint19,03userid用户IDbigint19,04groupid群组IDbigint19,05version版本varchar166ipIP地址varchar157firstping第一次发送的IP地址bigint19,08lastping最后一次发送的IP地址bigint19,09lastmessageping最后一次发送信息的IP地址bigint19,010sid发送varchar3211course课程IDbigint19,012lang语言类型varchar30mdl_choice描述：投票信息表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02course课程IDbigint19,03name投票名称varchar2554text详细内容text655355format是否格式化tinyint3,06publish公布结果tinyint3,07showresults是否显示结果tinyint3,08display是否显示smallint5,09allowupdate是否允许修改投票tinyint3,010showunanswered是否显示未回答的选项tinyint3,011limitanswers是否限制回答tinyint3,012timeopen打开投票时间bigint19,013timeclose关闭时间bigint19,014timemodified编辑时间bigint19,0mdl_choice_answers描述：投票回答记录表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02choiceid投票IDbigint19,03userid用户IDbigint19,04optionid选项IDbigint19,05timemodified编辑时间bigint19,0mdl_choice_options描述：投票选项表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02choiceid投票IDbigint19,03text投票内容说明text655354maxanswers课程的最大问题数bigint19,05timemodified编辑时间bigint19,0mdl_config描述：配置表序号字段名称字段描述字段类型长度允许空缺省值1idIDbigint19,02name参数名称varchar2553value值text65535mdl_config_plugins描述：插件配置表序号字段名称字段描述字段类型长度允许空缺省值1idIDbigint19,02plugin插件名称varchar100core3name参数名称varchar1004value值text65535mdl_context描述：正文级别与事例对应表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02contextlevel指明是哪一种：用户，课程，模块，还是系统，bigint19,0否03instanceid事件IDbigint19,0否04path路径varchar2555depth深度tinyint2否0mdl_context_rel描述：权限关系表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02c1正文ID1bigint19,0否3c2正文ID2bigint19,0否mdl_course描述：课程设置序号字段名称字段描述字段类型长度允许空缺省值1id课程IDbigint19,02category课程分类IDbigint19,03sortorder排序编号bigint19,04password选课密码varchar505fullname课程名全称varchar2546shortname课程名简称varchar1007idnumber课程编码IDvarchar1008summary课程概要text655359format课程格式varchar10topics10showgrades是否显示成绩tinyint3,0111modinfo课程页显示的相关功能模块代码信息longtext429496729512newsitems显示的新闻条目数mediumint7,0113teacher指定编辑权限教师varchar100Teacher14teachers无编辑权限教师varchar100Teachers15student指定编辑权限学生varchar100Student16students无编辑权限学生varchar100Students17guest是否允许访客进入课程tinyint3,018startdate课程开始时间bigint19,019enrolperiod选课时间限制天数bigint19,020numsections星期/主题的数目mediumint7,0121marker课程标记bigint19,022maxbytes最大上传文件bigint19,023showreports是否显示活动报表smallint5,024visible课程有效性，是否允许学生学习tinyint3,0125hiddensections隐藏的课程内容模块的方式tinyint3,026groupmode小组模式smallint5,027groupmodeforce强制小组模式smallint5,028lang指定课程语言类型varchar3029theme主题varchar5030cost课程费用varchar1031currency费用货币单位varchar3USD32timecreated课程创建时间bigint19,033timemodified课程修改时间bigint19,034metacourse该课程是元课程么tinyint3,035requested申请tinyint3,036restrictmodules是否限制模块tinyint3,037expirynotify选课期满通告,是否启用通告tinyint3,038expirythreshold课程到期后期限bigint19,039notifystudents选课期满通告,是否提醒学生tinyint3,040enrollable课程是否可以被选修tinyint3,0141enrolstartdate选课开始时间bigint19,042enrolenddate选课结束时间bigint19,043enrol选课设置选课插件varchar2044defaultrole选课设置默认角色bigint19,0mdl_course_allowed_modules描述：课程允许的模块序号字段名称字段描述字段类型长度允许空缺省值1idIDbigint19,02course课程IDbigint19,03module模块IDbigint19,0mdl_course_categories描述：课程分类序号字段名称字段描述字段类型长度允许空缺省值1id分类IDbigint19,02name类别名称varchar2553description类别描述text655354parent父类别bigint19,05sortorder排序编号bigint19,06coursecount此类别里的课程数bigint19,07visible是否可见tinyint3,018timemodified编辑时间bigint19,09depth分类深度，即几级分类bigint19,010path分类路径varchar255mdl_course_display描述：课程显示关系表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02course课程IDbigint19,03userid用户IDbigint19,04display是否显示bigint19,0mdl_course_meta描述：课程关系表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02Parent_course上一级课程bigint19,03child_course子课程bigint19,0mdl_course_modules描述：课程模块序号字段名称字段描述字段类型长度允许空缺省值1idIDbigint19,02course课程IDbigint19,03module模块IDbigint19,04instance此模块的第几个例子的IDbigint19,05section章节IDbigint19,06added添加时间bigint19,07score成绩分数smallint5,08indent缩进mediumint7,09visible是否可见tinyint3,0110visibleoldtinyint3,0111groupmode小组模式smallint5,0mdl_course_request描述：课程请求表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02fullname完整名字varchar2543shortname短名字varchar154summary摘要text655355reason原因text655356requester申请者bigint19,07password密码varchar50mdl_course_sections描述：课程章节序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02course课程IDbigint19,03section章节IDbigint19,04summary章节描述text655355sequence章节里资源活动的排序text655356visible章节是否可见tinyint3,01mdl_data描述：MOODLE数据配置表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02course课程IDbigint19,03name名称varchar2554intro简介text655355comments是否开通评论smallint5,06timeavailablefrom从什么时间开始bigint19,07timeavailableto到什么时间bigint19,08timeviewfrom查看开始时间bigint19,09timeviewto查看到什么时间bigint19,010requiredentries所需条目int10,011requiredentriestoview所需查阅的条目int10,012maxentries最大条目int10,013rssarticlesRSS文章条目smallint5,014singletemplate单模板text6553515listtemplate列表模板text6553516listtemplateheader列表模板头部text6553517listtemplatefooter列表模板底部text6553518addtemplate增加模板text6553519rsstemplateRss模板text6553520rsstitletemplateRss标题模板text6553521csstemplateCss模板text6553522jstemplateJs模板text6553523approval批准标志smallint5,024scale规模bigint19,025assessed评估bigint19,026defaultsort默认排序bigint19,027defaultsortdir默认排序目录smallint5,028editany编辑任何smallint5,0mdl_data_comments描述：数据评论表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02userid用户IDbigint19,03recordid记录IDbigint19,04content内容text655355format格式化标志tinyint3,06created创建者bigint19,07modified编辑者bigint19,0mdl_data_content描述：数据内容表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02fieldid字段IDbigint19,03recordid记录IDbigint19,04content内容0longtext42949672955content1内容1longtext42949672956content2内容2longtext42949672957content3内容3longtext42949672958content4内容4longtext4294967295mdl_data_fields描述：数据字段表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02dataid数据ID，data表bigint19,03type字段类型varchar2554name字段名称varchar2555description字段描述text655356param1参数1text655357param2参数2text655358param3参数3text655359param4参数4text6553510param5参数5text6553511param6参数6text6553512param7参数7text6553513param8参数8text6553514param9参数9text6553515param10参数10text65535mdl_data_ratings描述：数据评分表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02userid用户IDbigint19,03recordid记录IDbigint19,04rating等级/成绩bigint19,0mdl_data_records描述：数据记录表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02userid用户IDbigint19,03groupid分组IDbigint19,04dataid数据IDbigint19,05timecreated创建时间bigint19,06timemodified编辑时间bigint19,07approved批准标志smallint5,0mdl_enrol_authorize描述：登记授权表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02paymentmethod支付方式enum6cc3refundinfo退款信息smallint5,04ccname抄送名称varchar2555courseid课程IDbigint19,06userid用户IDbigint19,07transid传输ID值bigint19,08status状态bigint19,09timecreated创建时间bigint19,010settletime停留时间bigint19,011amount金额varchar1012currency货币类型varchar3USDmdl_enrol_authorize_refunds描述：退款表序号字段名称字段描述字段类型长度允许空缺省值1id自增量编号bigint19,02orderid订单IDbigint19,03status状态tinyint3,04amount金额varchar105transid传输IDbigint19,06settletime停留时间bigint19,0mdl_enrol_paypal描述：贝宝支