By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS org_roles (
`org_id` INT NOT NULL,
`label` VARCHAR(32) NOT NULL,
PRIMARY KEY (`org_id`, `label`))
ENGINE = InnoDB;
-- The users are unique
CREATE TABLE IF NOT EXISTS users (
`id` INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
UNIQUE INDEX id_UNIQUE (`id` ASC));
-- The orgs are unique
CREATE TABLE IF NOT EXISTS organizations (
`id` INT NOT NULL AUTO_INCREMENT,
`name` TEXT(64) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX id_UNIQUE (`id` ASC))
ENGINE = InnoDB;
-- The table that match a user in an org with a specified role (= label in the org_role table)
CREATE TABLE IF NOT EXISTS user_in_org (
`user_id` INT NOT NULL,
`org_id` INT NOT NULL,
`role` TEXT(32) NOT NULL,
PRIMARY KEY (`user_id`, `org_id`),
UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC));
ALTER TABLE user_in_org ADD
(CONSTRAINT fk_user_in_org__users1
FOREIGN KEY (`user_id`) REFERENCES users(`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_user_in_org__organizations1