微服务项目搭建二:数据库设计

最新推荐文章于 2024-06-18 23:06:44 发布

梦.之.蓝

最新推荐文章于 2024-06-18 23:06:44 发布

阅读量472

点赞数

分类专栏：教育项目搭建文章标签：数据库 database sql

本文链接：https://blog.csdn.net/qq_34790227/article/details/125122250

版权

教育项目搭建专栏收录该内容

3 篇文章 0 订阅

订阅专栏

01，人员基础信息和权限设计

2，课程相关数据库设计

数据库表信息为:

创建数据库，名字为: course

一,创建序列

create sequence base_case_questions_seq increment by 1 minvalue 1 no maxvalue start with 1;
create sequence base_examination_site_seq increment by 1 minvalue 1 no maxvalue start with 1;
create sequence base_multiple_choice_seq increment by 1 minvalue 1 no maxvalue start with 1;
create sequence base_profession_seq increment by 1 minvalue 1 no maxvalue start with 1;
create sequence base_rank_seq increment by 1 minvalue 1 no maxvalue start with 1;
create sequence base_subject_year_seq increment by 1 minvalue 1 no maxvalue start with 1;
create sequence base_thesis_title_seq increment by 1 minvalue 1 no maxvalue start with 1;
create sequence rel_examination_questions_seq increment by 1 minvalue 1 no maxvalue start with 1;

二,创建表

创建案例表:base_case_questions

-- ----------------------------
-- Table structure for base_case_questions
-- ----------------------------
DROP TABLE IF EXISTS "public"."base_case_questions";
CREATE TABLE "public"."base_case_questions" (
"id" int4 NOT NULL DEFAULT nextval('base_case_questions_seq'::regclass),
"serial_number" int4,
"content" text COLLATE "pg_catalog"."default",
"delete_status" int2 DEFAULT 0,
"create_time" varchar(255) COLLATE "pg_catalog"."default",
"update_time" varchar(255) COLLATE "pg_catalog"."default",
"create_id" int4,
"update_id" int4,
"subject_year_id" int4,
"question_one" varchar(255) COLLATE "pg_catalog"."default",
"question_one_url" varchar(255) COLLATE "pg_catalog"."default",
"question_two" varchar(255) COLLATE "pg_catalog"."default",
"question_two_url" varchar(255) COLLATE "pg_catalog"."default",
"question_three" varchar(255) COLLATE "pg_catalog"."default",
"question_three_url" varchar(255) COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "public"."base_case_questions"."id" IS '主键id';
COMMENT ON COLUMN "public"."base_case_questions"."serial_number" IS '题目序号，表示第几题';
COMMENT ON COLUMN "public"."base_case_questions"."content" IS '题目主体内容';
COMMENT ON COLUMN "public"."base_case_questions"."delete_status" IS '删除状态(0标识未删除,1表示已删除)';
COMMENT ON COLUMN "public"."base_case_questions"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."base_case_questions"."update_time" IS '修改时间';
COMMENT ON COLUMN "public"."base_case_questions"."create_id" IS '创建人的id';
COMMENT ON COLUMN "public"."base_case_questions"."update_id" IS '修改人的id';
COMMENT ON COLUMN "public"."base_case_questions"."subject_year_id" IS '课程年份id';
COMMENT ON COLUMN "public"."base_case_questions"."question_one" IS '问题一内容';
COMMENT ON COLUMN "public"."base_case_questions"."question_one_url" IS '问题一的图片url';
COMMENT ON COLUMN "public"."base_case_questions"."question_two" IS '问题二内容';
COMMENT ON COLUMN "public"."base_case_questions"."question_two_url" IS '问题二的图片url';
COMMENT ON COLUMN "public"."base_case_questions"."question_three" IS '问题三的内容';
COMMENT ON COLUMN "public"."base_case_questions"."question_three_url" IS '问题三的图片url';
COMMENT ON TABLE "public"."base_case_questions" IS '案例题表';

-- ----------------------------
-- Records of base_case_questions
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table base_case_questions
-- ----------------------------
ALTER TABLE "public"."base_case_questions" ADD CONSTRAINT "base_case_questions_pkey" PRIMARY KEY ("id");

创建考点表:base_examination_site

-- ----------------------------
-- Table structure for base_examination_site
-- ----------------------------
DROP TABLE IF EXISTS "public"."base_examination_site";
CREATE TABLE "public"."base_examination_site" (
"id" int4 NOT NULL DEFAULT nextval('base_examination_site_seq'::regclass),
"name" varchar(255) COLLATE "pg_catalog"."default",
"delete_status" int2 DEFAULT 0,
"create_time" varchar(255) COLLATE "pg_catalog"."default",
"update_time" varchar(255) COLLATE "pg_catalog"."default",
"create_id" int4,
"update_id" int4,
"video_url" varchar(255) COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "public"."base_examination_site"."id" IS '主键id';
COMMENT ON COLUMN "public"."base_examination_site"."name" IS '考点说明';
COMMENT ON COLUMN "public"."base_examination_site"."delete_status" IS '删除状态(0标识未删除,1表示已删除)';
COMMENT ON COLUMN "public"."base_examination_site"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."base_examination_site"."update_time" IS '修改时间';
COMMENT ON COLUMN "public"."base_examination_site"."create_id" IS '创建人的id';
COMMENT ON COLUMN "public"."base_examination_site"."update_id" IS '修改人的id';
COMMENT ON COLUMN "public"."base_examination_site"."video_url" IS '关联的视频说明的url';
COMMENT ON TABLE "public"."base_examination_site" IS '考点表';

-- ----------------------------
-- Records of base_examination_site
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table base_examination_site
-- ----------------------------
ALTER TABLE "public"."base_examination_site" ADD CONSTRAINT "base_examination_site_pkey" PRIMARY KEY ("id");

创建选择题表:base_multiple_choice

-- ----------------------------
-- Table structure for base_multiple_choice
-- ----------------------------
DROP TABLE IF EXISTS "public"."base_multiple_choice";
CREATE TABLE "public"."base_multiple_choice" (
"id" int4 NOT NULL DEFAULT nextval('base_multiple_choice_seq'::regclass),
"serial_number" int4,
"content" text COLLATE "pg_catalog"."default",
"answer" varchar(255) COLLATE "pg_catalog"."default",
"type" int2 DEFAULT 1,
"a_choice" varchar(255) COLLATE "pg_catalog"."default",
"b_choice" varchar(255) COLLATE "pg_catalog"."default",
"c_choice" varchar(255) COLLATE "pg_catalog"."default",
"d_choice" varchar(255) COLLATE "pg_catalog"."default",
"image_url" varchar(255) COLLATE "pg_catalog"."default",
"delete_status" int2 DEFAULT 0,
"create_time" varchar(255) COLLATE "pg_catalog"."default",
"update_time" varchar(255) COLLATE "pg_catalog"."default",
"create_id" int4,
"update_id" int4,
"subject_year_id" int4
)
;
COMMENT ON COLUMN "public"."base_multiple_choice"."id" IS '题目表的主键id';
COMMENT ON COLUMN "public"."base_multiple_choice"."serial_number" IS '题目序号，表示第几题';
COMMENT ON COLUMN "public"."base_multiple_choice"."content" IS '内容';
COMMENT ON COLUMN "public"."base_multiple_choice"."answer" IS '答案(A，B，C，D)中进行选择';
COMMENT ON COLUMN "public"."base_multiple_choice"."type" IS '是否是有图片的(1表示无图片，2表示有图片)';
COMMENT ON COLUMN "public"."base_multiple_choice"."a_choice" IS '选项a的内容';
COMMENT ON COLUMN "public"."base_multiple_choice"."b_choice" IS '选项b的内容';
COMMENT ON COLUMN "public"."base_multiple_choice"."c_choice" IS '选项c的内容';
COMMENT ON COLUMN "public"."base_multiple_choice"."d_choice" IS '选项d的内容';
COMMENT ON COLUMN "public"."base_multiple_choice"."image_url" IS '图片存储的url';
COMMENT ON COLUMN "public"."base_multiple_choice"."delete_status" IS '删除状态(0标识未删除,1表示已删除)';
COMMENT ON COLUMN "public"."base_multiple_choice"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."base_multiple_choice"."update_time" IS '修改时间';
COMMENT ON COLUMN "public"."base_multiple_choice"."create_id" IS '创建人的id';
COMMENT ON COLUMN "public"."base_multiple_choice"."update_id" IS '修改人的id';
COMMENT ON COLUMN "public"."base_multiple_choice"."subject_year_id" IS '课程年份id';
COMMENT ON TABLE "public"."base_multiple_choice" IS '选择题表';

-- ----------------------------
-- Records of base_multiple_choice
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table base_multiple_choice
-- ----------------------------
ALTER TABLE "public"."base_multiple_choice" ADD CONSTRAINT "base_subject_pkey" PRIMARY KEY ("id");

创建职业表:base_profession

-- ----------------------------
-- Table structure for base_profession
-- ----------------------------
DROP TABLE IF EXISTS "public"."base_profession";
CREATE TABLE "public"."base_profession" (
"id" int4 NOT NULL DEFAULT nextval('base_profession_seq'::regclass),
" professional_name" varchar(255) COLLATE "pg_catalog"."default",
"delete_status" int2 DEFAULT 0,
"create_time" varchar(255) COLLATE "pg_catalog"."default",
"update_time" varchar(255) COLLATE "pg_catalog"."default",
"create_id" int4,
"update_id" int4
)
;
COMMENT ON COLUMN "public"."base_profession"."id" IS '职业id';
COMMENT ON COLUMN "public"."base_profession"." professional_name" IS '职业名字';
COMMENT ON COLUMN "public"."base_profession"."delete_status" IS '删除状态(0标识未删除,1表示已删除)';
COMMENT ON COLUMN "public"."base_profession"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."base_profession"."update_time" IS '修改时间';
COMMENT ON COLUMN "public"."base_profession"."create_id" IS '创建人的id';
COMMENT ON COLUMN "public"."base_profession"."update_id" IS '修改人的id';
COMMENT ON TABLE "public"."base_profession" IS '职业表';

-- ----------------------------
-- Records of base_profession
-- ----------------------------
INSERT INTO "public"."base_profession" VALUES (1, '软件工程师', 0, NULL, NULL, NULL, NULL);

-- ----------------------------
-- Primary Key structure for table base_profession
-- ----------------------------
ALTER TABLE "public"."base_profession" ADD CONSTRAINT "base_profession_info_pkey" PRIMARY KEY ("id");

创建职业等级表:base_rank

-- ----------------------------
-- Table structure for base_rank
-- ----------------------------
DROP TABLE IF EXISTS "public"."base_rank";
CREATE TABLE "public"."base_rank" (
"id" int4 NOT NULL DEFAULT nextval('base_rank_seq'::regclass),
"name" varchar(255) COLLATE "pg_catalog"."default",
"profession_id" int4,
"delete_status" int2 DEFAULT 0,
"create_time" varchar(255) COLLATE "pg_catalog"."default",
"update_time" varchar(255) COLLATE "pg_catalog"."default",
"create_id" int4,
"update_id" int4,
"level" varchar(255) COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "public"."base_rank"."id" IS '主键id';
COMMENT ON COLUMN "public"."base_rank"."name" IS '职级名词';
COMMENT ON COLUMN "public"."base_rank"."profession_id" IS '职业id';
COMMENT ON COLUMN "public"."base_rank"."delete_status" IS '删除状态(0标识未删除,1表示已删除)';
COMMENT ON COLUMN "public"."base_rank"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."base_rank"."update_time" IS '修改时间';
COMMENT ON COLUMN "public"."base_rank"."create_id" IS '创建人的id';
COMMENT ON COLUMN "public"."base_rank"."update_id" IS '修改人的id';
COMMENT ON COLUMN "public"."base_rank"."level" IS '所属级别(初级，终极，高级，无级别)';
COMMENT ON TABLE "public"."base_rank" IS '职业等级表';

-- ----------------------------
-- Records of base_rank
-- ----------------------------
INSERT INTO "public"."base_rank" VALUES (1, '系统架构师', 1, 0, NULL, NULL, NULL, NULL, '高级');

-- ----------------------------
-- Primary Key structure for table base_rank
-- ----------------------------
ALTER TABLE "public"."base_rank" ADD CONSTRAINT "base_rank_pkey" PRIMARY KEY ("id");

创建职业级别关联年份表:base_subject_year

-- ----------------------------
-- Table structure for base_subject_year
-- ----------------------------
DROP TABLE IF EXISTS "public"."base_subject_year";
CREATE TABLE "public"."base_subject_year" (
"id" int4 NOT NULL DEFAULT nextval('base_subject_year_seq'::regclass),
"rank_id" int4,
"year" int4,
"type" varchar(255) COLLATE "pg_catalog"."default",
"delete_status" int2 DEFAULT 0,
"create_time" varchar(255) COLLATE "pg_catalog"."default",
"update_time" varchar(255) COLLATE "pg_catalog"."default",
"create_id" int4,
"update_id" int4
)
;
COMMENT ON COLUMN "public"."base_subject_year"."id" IS '主键id';
COMMENT ON COLUMN "public"."base_subject_year"."rank_id" IS '职级表id';
COMMENT ON COLUMN "public"."base_subject_year"."year" IS '年份';
COMMENT ON COLUMN "public"."base_subject_year"."type" IS '(上，下)';
COMMENT ON COLUMN "public"."base_subject_year"."delete_status" IS '删除状态(0标识未删除,1表示已删除)';
COMMENT ON COLUMN "public"."base_subject_year"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."base_subject_year"."update_time" IS '修改时间';
COMMENT ON COLUMN "public"."base_subject_year"."create_id" IS '创建人的id';
COMMENT ON COLUMN "public"."base_subject_year"."update_id" IS '修改人的id';

-- ----------------------------
-- Records of base_subject_year
-- ----------------------------
INSERT INTO "public"."base_subject_year" VALUES (1, 1, 2010, '下', 0, NULL, NULL, NULL, NULL);

-- ----------------------------
-- Primary Key structure for table base_subject_year
-- ----------------------------
ALTER TABLE "public"."base_subject_year" ADD CONSTRAINT "base_subject_year_pkey" PRIMARY KEY ("id");

创建论文题表:base_thesis_title

-- ----------------------------
-- Table structure for base_thesis_title
-- ----------------------------
DROP TABLE IF EXISTS "public"."base_thesis_title";
CREATE TABLE "public"."base_thesis_title" (
"id" int4 DEFAULT nextval('base_thesis_title_seq'::regclass),
"content" text COLLATE "pg_catalog"."default",
"question_one" text COLLATE "pg_catalog"."default",
"delete_status" int2 DEFAULT 0,
"create_time" varchar(255) COLLATE "pg_catalog"."default",
"update_time" varchar(255) COLLATE "pg_catalog"."default",
"create_id" int4,
"update_id" int4,
"subject_year_id" int4
)
;
COMMENT ON COLUMN "public"."base_thesis_title"."id" IS '主键id';
COMMENT ON COLUMN "public"."base_thesis_title"."content" IS '题目内容';
COMMENT ON COLUMN "public"."base_thesis_title"."question_one" IS '问题1内容';
COMMENT ON COLUMN "public"."base_thesis_title"."delete_status" IS '删除状态(0标识未删除,1表示已删除)';
COMMENT ON COLUMN "public"."base_thesis_title"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."base_thesis_title"."update_time" IS '修改时间';
COMMENT ON COLUMN "public"."base_thesis_title"."create_id" IS '创建人的id';
COMMENT ON COLUMN "public"."base_thesis_title"."update_id" IS '修改人的id';
COMMENT ON COLUMN "public"."base_thesis_title"."subject_year_id" IS '课程年份id';
COMMENT ON TABLE "public"."base_thesis_title" IS '论文题表';

-- ----------------------------
-- Records of base_thesis_title
-- ----------------------------

创建选择题关联考点表:rel_examination_questions

-- ----------------------------
-- Table structure for rel_examination_questions
-- ----------------------------
DROP TABLE IF EXISTS "public"."rel_examination_questions";
CREATE TABLE "public"."rel_examination_questions" (
"id" int4 DEFAULT nextval('rel_examination_questions_seq'::regclass),
"multiple_choice_id" int4,
"examination_site_id" varchar(255) COLLATE "pg_catalog"."default",
"delete_status" int2 DEFAULT 0,
"create_time" varchar(255) COLLATE "pg_catalog"."default",
"update_time" varchar(255) COLLATE "pg_catalog"."default",
"create_id" int4,
"update_id" int4
)
;
COMMENT ON COLUMN "public"."rel_examination_questions"."id" IS '主键id';
COMMENT ON COLUMN "public"."rel_examination_questions"."multiple_choice_id" IS '选择题的id';
COMMENT ON COLUMN "public"."rel_examination_questions"."examination_site_id" IS '考点id';
COMMENT ON COLUMN "public"."rel_examination_questions"."delete_status" IS '删除状态(0标识未删除,1表示已删除)';
COMMENT ON COLUMN "public"."rel_examination_questions"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."rel_examination_questions"."update_time" IS '修改时间';
COMMENT ON COLUMN "public"."rel_examination_questions"."create_id" IS '创建人的id';
COMMENT ON COLUMN "public"."rel_examination_questions"."update_id" IS '修改人的id';

-- ----------------------------
-- Records of rel_examination_questions

三,把序列赋值给表

alter table "public"."base_case_questions" alter column "id" set default nextval('base_case_questions_seq');
alter table "public"."base_examination_site" alter column "id" set default nextval('base_examination_site_seq');
alter table "public"."base_multiple_choice" alter column "id" set default nextval('base_multiple_choice_seq');
alter table "public"."base_profession" alter column "id" set default nextval('base_profession_seq');
alter table "public"."base_rank" alter column "id" set default nextval('base_rank_seq');
alter table "public"."base_subject_year" alter column "id" set default nextval('base_subject_year_seq');
alter table "public"."base_thesis_title" alter column "id" set default nextval('base_thesis_title_seq');
alter table "public"."rel_examination_questions" alter column "id" set default nextval('rel_examination_questions_seq');