表设计之权限管理

今天来讲讲数据表设计中,对于用户的权限管理表的设计.

• 用户表和权限表,用户表中的用户可以有权限表中的多个权限(如刘德华可以有安卓浏览/安卓发帖/安卓评论等权限),是一对多的关系,而反过来权限表的安卓浏览这个权限,同样也可以适用于用户表的不同用户(如刘德华和杨幂都可以有安卓浏览的权限),因此就需要设计一张用户权限的关联表,但同时因为一张关系表进行对应,会使得过多重复数据(例刘德华拥有安卓的所有权限,而杨幂有所有平台的权限),使得查询效率降低,因此在此过程中加入角色表,用于连接用户表和权限表的中间表,因为加入这张中间表,因此需要增加用户角色关系表和角色权限关系表.
• 
• 权限管理需要用到五张表,其中包含三张主表(用户表,角色表,权限表)和两张关系表(用户角色关系表,角色权限关系表)
• 创建三张主表:
  • 用户表
  • 角色表
  • 权限表

create database boot03 charset=utf8;
    use boot03;
    create table user(id int primary key auto_increment,name varchar(50));

create table role(id int primary key auto_increment, name varchar(50));

create table module(id int primary key auto_increment, name varchar(50));

• 创建两张关系表:
  • 用户角色关系表
  • 角色权限关系表

create table u_r(uid int, rid int);

create table r_m(rid int,mid int);

• 主表准备数据:

insert into user values(null,'刘德华'),(null,'张学友'),(null,'杨幂');

insert into role values(null,'安卓管理员'),(null,'苹果游客'),(null,'总管理员');

insert into module values(null,'安卓浏览'),(null,'安卓评论'),(null,'安卓发帖'),(null,'安卓删帖'),(null,'苹果浏览'),(null,'苹果评论'),(null,'苹果发帖'),(null,'苹果删帖')

• 关系表中添加数据:
  • 刘德华->安卓管理员和苹果游客       张学友->苹果游客     杨幂->总管理员

 insert into u_r values(1,1),(1,2),(2,2),(3,3);

 insert into r_m values(1,1),(1,2),(1,3),(1,4),(2,5),(2,6),(3,1),(3,2),(3,3),(3,4),(3,5),(3,6),(3,7),(3,8);

关联查询练习

1. 查询刘德华的角色
   

   select r.name
   
   from user u join u_r ur  on u.id=ur.uid
   
   join role r on r.id=ur.rid
   
   where u.name='刘德华';

2. 查询苹果游客这个角色对应的用户是谁? 

   select u.name
   
   from user u join u_r ur  on u.id=ur.uid
   
   join role r on r.id=ur.rid
   
   where r.name='苹果游客';

3. 查询总管理员的权限有哪些?

   select m.name
   
   from role r join r_m rm on r.id=rm.rid
   
   join module m on m.id=rm.mid
   
   where r.name='总管理员';

4. 查询苹果浏览权限对应的角色有哪些?

   select r.name
   
   from role r join r_m rm on r.id=rm.rid
   
   join module m on m.id=rm.mid
   
   where m.name='苹果浏览';

5. 查询刘德华的权限有哪些?

   select m.name
   
   from user u join u_r ur on u.id=ur.uid
   
   join r_m rm on ur.rid = rm.rid
   
   join module m on m.id=rm.mid
   
   where u.name='刘德华';

6. 查询杨幂的权限有哪些?

   select m.name
   
   from user u join u_r ur on u.id=ur.uid
   
   join r_m rm on ur.rid = rm.rid
   
   join module m on m.id=rm.mid
   
   where u.name='杨幂';

7. 查询有哪些用户拥有安卓删帖权限?

   select u.name
   
   from user u join u_r ur on u.id=ur.uid
   
   join r_m rm on ur.rid = rm.rid
   
   join module m on m.id=rm.mid
   
   where m.name='安卓删帖';