Potential database schema for openbadger API

schema.sql

DROP TABLE IF EXISTS `system`;
CREATE TABLE `system` (
  `systemId` INT NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(20) UNIQUE NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `image` BLOB,
  `deleted` BOOLEAN NOT NULL,
  PRIMARY KEY (`systemId`)
);

DROP TABLE IF EXISTS `issuer`;
CREATE TABLE `issuer` (
  `issuerId` INT NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(20) UNIQUE NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `strapline` TEXT,
  `description` TEXT,
  `url` VARCHAR(255),
  `image` BLOB,
  `deleted` BOOLEAN NOT NULL,
  PRIMARY KEY (`issuerId`)
);

DROP TABLE IF EXISTS `program`;
CREATE TABLE `program` (
  `programId` INT NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(20) UNIQUE NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `strapline` TEXT,
  `description` TEXT,
  `url` VARCHAR(255),
  `startDate` DATE,
  `endDate` DATE,
  `image` BLOB,
  `deleted` BOOLEAN NOT NULL,
  PRIMARY KEY (`programId`)
);

DROP TABLE IF EXISTS `issuerInSystem`;
CREATE TABLE `issuerInSystem` (
  `systemId` INT NOT NULL REFERENCES `system`(`systemId`),
  `issuerId` INT NOT NULL REFERENCES `issuer`(`issuerId`),
  PRIMARY KEY (`systemId`, `issuerId`)
);

DROP TABLE IF EXISTS `issuerInProgram`;
CREATE TABLE `issuerInProgram` (
  `issuerId` INT NOT NULL REFERENCES `issuer`(`issuerId`),
  `programId` INT NOT NULL REFERENCES `program`(`programId`),
  PRIMARY KEY (`issuerId`, `programId`)
);

DROP TABLE IF EXISTS programInSystem;
CREATE TABLE programInSystem (
  `systemId` INT NOT NULL REFERENCES `system`(`systemId`),
  `programId` INT NOT NULL REFERENCES `program`(`programId`),
  PRIMARY KEY (`systemId`, `programId`)
);

DROP TABLE IF EXISTS user;
CREATE TABLE user (
  `userId` INT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `super` BOOLEAN NOT NULL,
  PRIMARY KEY (`userId`)
);

DROP TABLE IF EXISTS admin;
CREATE TABLE admin (
  `adminId` INT NOT NULL AUTO_INCREMENT,
  `userId` INT NOT NULL REFERENCES `user`(`userId`),
  `entityType` ENUM('SYSTEM', 'ISSUER', 'PROGRAM') NOT NULL,
  `entityId` INT NOT NULL,
  PRIMARY KEY (`adminId`),
  UNIQUE KEY `relationship` (`userId`, `entityType`, `entityId`)
);