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;
设计图:
附件: