老翟

sqlDROP DATABASE IF EXISTS `local` ; 

CREATE DATABASE `local`; 

use `local` ; 

DROP TABLE IF EXISTS actionmanager; 

CREATE TABLE actionmanager(
    actionid INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    actionName VARCHAR(255) NOT NULL ,
    action VARCHAR(255) NOT NULL,
    createDate DATE,
    viewmode INT DEFAULT 0
    #index inx(`action`)
)type=InnoDB; 

DROP TABLE IF EXISTS actioncolumn ; 

CREATE TABLE actioncolumn(
    actioncolumnid INT AUTO_INCREMENT NOT NULL PRIMARY KEY ,
    actioncolumnname VARCHAR(255) NOT NULL
)type=InnoDB; 

DROP TABLE IF EXISTS groupmanager; 

CREATE TABLE groupmanager(
    groupid INT AUTO_INCREMENT NOT NULL PRIMARY KEY ,
    groupname VARCHAR(255) NOT NULL,
    groupinfo VARCHAR(255) DEFAULT NULL,
    masterid INT NOT NULL,    #who created this group
    mastername VARCHAR(255),
    createdate DATE 
)type=InnoDB; 

DROP TABLE IF EXISTS master; 

CREATE TABLE master(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL ,
    password VARCHAR(255) NOT NULL ,
    sex VARCHAR(255) NOT NULL ,
    position VARCHAR(255) NOT NULL,
    masterid INT ,             #whoe created this master    
    mastername VARCHAR(255),
    createdate DATE
)type=InnoDB; 

DROP TABLE IF EXISTS  actiongroup ; 

CREATE TABLE actiongroup(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `action` VARCHAR(255) NOT NULL, 
    groupid INT NOT NULL ,
    masterid int NOT NULL,
    mastername VARCHAR(255) NOT NULL ,
    createdate DATE,
    index inx_ag(`action`)
)type=InnoDB; 

DROP TABLE IF EXISTS mastergroup ; 

CREATE TABLE mastergroup(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    masterid INT NOT NULL ,
    groupid INT NOT NULL ,
    masterid2 INT NOT NULL ,  # who created or modified this mastergroup 
    creatDate DATE 
)type=InnoDB ; 

##############action link group ###################### 

CREATE INDEX idx_actionmanager_action ON actionmanager(`action`); 
CREATE INDEX idx_groupmanager_groupid ON groupmanager(`groupid`); 

ALTER TABLE actiongroup 
ADD CONSTRAINT  fk_action
FOREIGN KEY (action) REFERENCES actionmanager(`action`) 
ON DELETE CASCADE ON UPDATE CASCADE; 

ALTER TABLE actiongroup 
ADD CONSTRAINT  fk_groupid
FOREIGN KEY (groupid) REFERENCES groupmanager(`groupid`) 
ON DELETE CASCADE ON UPDATE CASCADE; 

##############action link  master###################### 

CREATE INDEX idx_master_id ON master(`id`); 

ALTER TABLE mastergroup 
ADD CONSTRAINT  fk_masterg_mid
FOREIGN KEY (masterid) REFERENCES master(`id`) 
ON DELETE CASCADE ON UPDATE CASCADE; 

ALTER TABLE mastergroup 
ADD CONSTRAINT  fk_masterg_gid
FOREIGN KEY (groupid) REFERENCES groupmanager(`groupid`) 
ON DELETE CASCADE ON UPDATE CASCADE;