web权限数据库设计，web权限数据库

web权限数据库设计，web权限数据库

CREATE TABLE
    account
    (
        accid INT DEFAULT '0' NOT NULL,
        accCode CHAR(20),
        accName VARCHAR(30),
        PRIMARY KEY (accid)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE
    role
    (
        roleid INT DEFAULT '0' NOT NULL,
        roleCode CHAR(20),
        roleName VARCHAR(30),
        PRIMARY KEY (roleid)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE
    accrole
    (
        accroleid INT DEFAULT '0' NOT NULL,
        faccid INT,
        froleid INT,
        PRIMARY KEY (accroleid),
        CONSTRAINT AccRole_fk1 FOREIGN KEY (faccid) REFERENCES account (accid) ,
        CONSTRAINT AccRole_fk2 FOREIGN KEY (froleid) REFERENCES role (roleid),
        INDEX AccRole_fk1 (faccid),
        INDEX AccRole_fk2 (froleid)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;
	CREATE TABLE
    module
    (
        modid INT DEFAULT '0' NOT NULL,
        modCode CHAR(20),
        modName VARCHAR(30),
        parentCode CHAR(20),
        url VARCHAR(50),
        PRIMARY KEY (modid)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE
    privilege
    (
        priid INT DEFAULT '0' NOT NULL,
        priCode CHAR(20),
        priName VARCHAR(30),
        PRIMARY KEY (priid)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;
	
CREATE TABLE
    modpri
    (
        modpriid INT DEFAULT '0' NOT NULL,
        modid INT,
        priid INT,
        PRIMARY KEY (modpriid),
        CONSTRAINT modpri_fk1 FOREIGN KEY (modid) REFERENCES module (modid) ,
        CONSTRAINT modpri_fk2 FOREIGN KEY (priid) REFERENCES privilege (priid),
        INDEX modpri_fk1 (modid),
        INDEX modpri_fk2 (priid)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE
    rolemodpri
    (
        rolemodpri INT DEFAULT '0' NOT NULL,
        roleid INT,
        modpriid INT,
        PRIMARY KEY (rolemodpri),
        CONSTRAINT rolemodpri_fk1 FOREIGN KEY (roleid) REFERENCES role (roleid) ,
        CONSTRAINT rolemodpri_fk2 FOREIGN KEY (modpriid) REFERENCES modpri (modpriid),
        INDEX rolemodpri_fk1 (roleid),
        INDEX rolemodpri_fk2 (modpriid)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

设计图：

附件：

http://download.csdn.net/detail/linzhichao12345/7741975

设计数据库带个user role, 不同权限走着不同的session, 根据不同的session 显示不同的内容。
 

最简单的情况所有权限明细放到一个表里，如下
USER( UID,A_ADD, A_UPD, A_DEL,B_ADD, B_UPD, B_DEL,C_ADD, C_UPD, C_DEL,D_ADD, D_UPD, D_DEL,E_ADD, E_UPD, E_DEL)