CREATE TABLE roles (
  roleID int(11) PRIMARY KEY AUTO_INCREMENT,
  name char(20) DEFAULT '',
  rolerank SMALLINT DEFAULT NULL
) ENGINE=InnoDB;
CREATE TABLE users (
  userID int(11) PRIMARY KEY AUTO_INCREMENT,
  username char(10) NOT NULL DEFAULT '',
  password char(10) NOT NULL DEFAULT '',
  user_username char(16) NOT NULL DEFAULT '',
  status SMALLINT NOT NULL DEFAULT '0',
  entered_by INT DEFAULT NULL
) ENGINE=InnoDB;
CREATE TABLE userroles (
  userroleID int(11) PRIMARY KEY AUTO_INCREMENT,
  roleID int(11) NOT NULL DEFAULT 0,
  userID int(11) NOT NULL DEFAULT 0,
  KEY ur_roleID (roleID),
  KEY ur_userID (userID),
  CONSTRAINT FOREIGN KEY (roleID) REFERENCES roles (roleID),
  CONSTRAINT FOREIGN KEY (userID) REFERENCES users (userID)
) ENGINE=InnoDB;
CREATE TABLE usecases (
  usecaseID int(11) PRIMARY KEY AUTO_INCREMENT,
  name char(50) NOT NULL DEFAULT '',
  description TEXT,
  roleID int(11) NOT NULL DEFAULT '0',
  KEY roleID (roleID),
  CONSTRAINT FOREIGN KEY (roleID) REFERENCES roles (roleID)
) ENGINE=InnoDB;

Last updated 22 May 2024