项目调研阶段已基本完成,接下来的数据库设计是整个项目开发中最重要的一个部分,如何设计出科学有效的数据表,对于我们来有点难度,只能借鉴他人优秀的设计思想,我负责的是用户权限控制这一块,表的设计我基本如下:
/*==============================================================*/
/* Table: tbl_function 功能表 */
/*==============================================================*/
create table tbl_function
(
fun_id varchar(6) not null,
type int(2),
status int(2),
parents_fun_id varchar(6),
fun_name varchar(20) not null,
url varchar(255),
post int(11),
remarks varcahr(200),
constraint PK_RBS_FUNCTION primary key (fun_id)
);
/*==============================================================*/
/* Table: tbl_gourp 权限组 */
/*==============================================================*/
create table tbl_gourp
(
id int(6) not null,
function_name char(20),
gourp char(20) not null,
remarks varchar(200),
primary key (id)
);
/*==============================================================*/
/* Table: tbl_user 用户表 */
/*==============================================================*/
create table tbl_user
(
id numeric not null,
class varchar(10),
level_type char(10),
if_teacher_charge char(2),
gourp_id int(6),
real_name varchar(20) not null,
sex char(2) not null,
old int(3) not null,
user varchar(20) not null,
password varchoar(18) not null,
repassword varchr(10) not null,
address varchar(50) not null,
tel varchar(15) not null,
phone varchar(20) not null,
email varchar(30) not null,
teach_date char(3) not null,
position varchar(10) not null,
department varchar(20),
level int(2),
certificate varchar(20),
birthday date,
birthplace varchar(50),
native varchar(20),
current_residence varchar(50),
indentity_id varchar(20),
graduate vvarchar(50),
come_date date,
contract_time varchar(10),
resume varchar(500),
evaluate varchar(500),
url varchar(30),
primary key (id)
);
权限控制涉及到的表就三张了,要不要加表,我就不太清楚了,表设计出来了如何在用户登录时给予不同权限控制,暂时还不太清楚如何实现。
--------------------------------------------------补充-------------------------------------------
看了很多资料,权限控制需要五张表来维护,补充表如下
create table tbl_role_fun
(
role_id varchar(6) not null,
fun_id varchar(6) not null,
constraint PK_RBS_ROLE_FUN primary key clustered (role_id, fun_id)
);
create table tbl_user_role
(
user_id varchar(6) not null,
role_id varchar(6) not null,
constraint PK_RBS_USER_ROLE primary key clustered (user_id, role_id)
);