TP support Menu Dynamic generation RBAC permission system database structure design scheme

The simplest database structure design based on RBAC permission system includes the following tables1.User Table--Table "T_user"DDL CREATE TABLE ' t_user ' (' id ' int (One) notNULLAuto_increment,' username ' varchar (100)DEFAULT NULL,' password ' varchar (100)DEFAULT NULL,' name ' varchar (100)DEFAULT NULL,' status ' int (11)DEFAULT' 0 ',' department_id ' int (11)DEFAULT NULL,' desc ' varchar (255)DEFAULT NULL,PRIMARYKEY(' id ')) ENGINE=innodbDEFAULTcharset=UTF8; 2.Permission Table--Table "T_permission"DDL CREATE TABLE ' t_permission ' (' id ' int (One) notNULLAuto_increment,' name ' varchar (100)DEFAULT NULL,' desc ' varchar (255)DEFAULT NULL,' status ' int (11)DEFAULT NULL,' is_menu_display ' int (11)DEFAULT NULL,' menu_url ' varchar (255)DEFAULT NULL,' main_menu_id ' int (11)DEFAULT NULL,PRIMARYKEY(' id ')) ENGINE=innodbDEFAULTcharset=UTF8; 3.Role Table--Table "T_role"DDL CREATE TABLE ' t_role ' (' id ' int (One) notNULLAuto_increment,' name ' varchar (100)DEFAULT NULL,' desc ' varchar (200)DEFAULT NULL,PRIMARYKEY(' id ')) ENGINE=innodbDEFAULTcharset=UTF8; 4.User Role Table--Table "T_user_role"DDL CREATE TABLE ' t_user_role ' (' user_id ' int (One) notNULL DEFAULT' 0 ',' role_id ' int (One) notNULL DEFAULT' 0 ',PRIMARYKEY(' user_id ',' role_id ')) ENGINE=innodbDEFAULTcharset=UTF8; 5.role Permission Table--Table "T_role_permission"DDL CREATE TABLE ' t_role_permission ' (' role_id ' int (One) notNULL DEFAULT' 0 ',' permission_id ' int (One) notNULL DEFAULT' 0 ',PRIMARYKEY(' role_id ',' permission_id ')) ENGINE=innodbDEFAULTcharset=UTF8; 6.Menu Table--Table "T_menu"DDL CREATE TABLE ' t_menu ' (' id ' int (One) notNULLAuto_increment,' name ' varchar (100)DEFAULT NULL,' desc ' varchar (255)DEFAULT NULL,PRIMARYKEY(' id ')) ENGINE=innodbDEFAULTcharset=UTF8; 7.Department Table--Table "T_department"DDL CREATE TABLE ' t_department ' (' id ' int (One) notNULLAuto_increment,' name ' varchar (100)DEFAULT NULL,' desc ' varchar (255)DEFAULT NULL,PRIMARYKEY(' id ')) ENGINE=innodbDEFAULTcharset=UTF8; The primary SQL involved: 1. Get a list of permissions for a user:Select* FROM T_permission where ID in (select distinct permission_id from T_role_permission where role_id in (select role_id From T_user_role where user_id=1))    2.main Menu list: (menu) Select*From T_menu where ID in (select distinct main_menu_id from t_permission where ID in (1,2,3,4))        3.a list of submenus under one of the main menus: select* FROM T_permission where ID in (1,2,3,4) and main_menu_id=1Select* FROM T_permission where ID in (1,2,3,4) and main_menu_id=2

Test data:

INSERT into ' t_department ' VALUES (' 1 ', ' technical ', ' technical ')); INSERT into ' t_department ' VALUES (' 2 ', ' Finance department ', ' finance Department '); INSERT into ' t_department ' VALUES (' 3 ', ' Design department ', ' design department '); INSERT into ' T_menu ' VALUES (' 1 ', ' System Management ', ' System Management '); INSERT into ' T_menu ' VALUES (' 2 ', ' Financial management ', ' financial management '); INSERT into ' T_menu ' VALUES (' 3 ', ' Department management ', ' departmental management '); INSERT into ' T_menu ' VALUES (' 4 ', ' Log management ', ' Log management '); INSERT into ' T_menu ' VALUES (' 5 ', ' Role management ', ' role management '); INSERT into ' t_permission ' VALUES (' 1 ', ' user list ', ' User list ', ' 0 ', ' 1 ', ' userlist.do ', ' 1 '); INSERT into ' t_permission ' VALUES (' 2 ', ' User new ', ' User new ', ' 0 ', ' 1 ', ' useradd.do ', ' 1 '); INSERT into ' t_permission ' VALUES (' 3 ', ' User modified ', ' User modified ', ' 0 ', ' 1 ', ' usermodify.do ', ' 1 '); INSERT into ' t_permission ' VALUES (' 4 ', ' User delete ', ' user delete ', ' 0 ', ' 1 ', ' userdel.do ', ' 2 '); INSERT into ' t_permission ' VALUES (' 5 ', ' User query ', ' User query ', ' 0 ', ' 1 ', ' usersearch.do ', ' 3 '); INSERT into ' t_role ' VALUES (' 1 ', ' System administrator ', ' System administrator '); INSERT into ' t_role ' VALUES (' 2 ', ' Test role 1 ', ' Test role 1 '); INSERT into ' t_role ' VALUES (' 3 ', ' Test role 2 ', ' Test role 2 '); INSERT into ' t_role_permission ' VALUES (' 1 ', ' 1 '); INSERT into ' t_role_permission ' VALUES (' 1 ', ' 2 '); INSERT into ' t_role_permission ' VALUES (' 1 ', ' 3 '); INSERT into ' t_role_permission ' VALUES (' 2 ', ' 2 '); INSERT into ' t_role_permission ' VALUES (' 3 ', ' 4 '); INSERT into ' T_user ' VALUES (' 1 ', ' admin ', ' admin ', ' admin ', ' 0 ', ' 1 ', ' admin '); INSERT into ' t_user_role ' VALUES (' 1 ', ' 1 '); INSERT into ' t_user_role ' VALUES (' 1 ', ' 2 '); INSERT into ' t_user_role ' VALUES (' 1 ', ' 3 ');

TP support Menu Dynamic generation RBAC permission system database structure design scheme