bysj/script.sql at main · lyl7780/bysj · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
create table if not exists attend
(
    attend_id int auto_increment
        primary key,
    doctor_id int  null,
    date      date not null,
    number    int  not null
);

create index doctor_id
    on attend (doctor_id, attend_id);

create table if not exists department
(
    office_id int auto_increment
        primary key,
    dname     varchar(20) not null,
    constraint name
        unique (office_id)
);

create table if not exists department_doctor
(
    id         int auto_increment
        primary key,
    department int null,
    doctor_id  int null
);

create index department_doctor_department_index
    on department_doctor (department);

create table if not exists menu
(
    id        bigint auto_increment
        primary key,
    parent_id bigint       null comment '父菜单ID，一级菜单为0',
    name      varchar(64)  not null,
    path      varchar(255) null comment '菜单URL',
    perms     varchar(255) null comment '授权(多个用逗号分隔，如：user:list,user:create',
    component varchar(255) null,
    type      int          not null comment '类型  0：目录  1：菜单  2：按钮',
    icon      varchar(32)  null comment '菜单图标',
    order_num int          null comment '排序',
    created   datetime     null,
    updated   datetime     null,
    status    int          not null,
    constraint name
        unique (name, id)
)
    charset = utf8;

create table if not exists `order`
(
    order_id         int auto_increment
        primary key,
    user_id          int                  null comment '预约的用户',
    attend_id        int                  null comment '预约哪天哪个医生',
    register_status  tinyint(1) default 0 null comment '是否报到',
    diagnosis_status tinyint(1) default 0 null comment '是否诊断',
    num              int                  null comment '排队号',
    diagnosis        varchar(2000)        null comment '诊断书'
);

create index attend_id
    on `order` (attend_id, order_id);

create table if not exists role
(
    id          bigint auto_increment
        primary key,
    name        varchar(64)          not null,
    role        varchar(64)          not null,
    description varchar(64)          null comment '备注',
    status      tinyint(1) default 1 not null,
    created     datetime             null,
    updated     datetime             null,
    constraint code
        unique (role)
)
    charset = utf8;

create table if not exists role_menu
(
    id      bigint auto_increment
        primary key,
    role_id bigint not null,
    menu_id bigint not null
);

create index role_menu_role_id_index
    on role_menu (role_id);

create table if not exists user
(
    id          int auto_increment
        primary key,
    avatar      varchar(255)         null comment '头像',
    username    varchar(20)          not null,
    password    varchar(200)         not null,
    name        varchar(20)          null,
    id_card     varchar(20)          null,
    phone       varchar(20)          null,
    description varchar(255)         null,
    status      tinyint(1) default 1 not null,
    cov         tinyint(1) default 0 null comment '是否感染新冠肺炎',
    created     datetime             null,
    updated     datetime             null,
    constraint username
        unique (username, id)
);

create table if not exists user_role
(
    id      bigint auto_increment
        primary key,
    user_id bigint not null,
    role_id bigint not null
);

create index user_role_user_id_index
    on user_role (user_id);