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)

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