1

I have the following database:

Database Design Diagram

DB EXPLANATION

  • Each user can be assigned multiple projects and scopes.
  • Each user can only be assigned one role.
  • Scopes are shared, so multiple roles can have the same scopes.
  • I created the roles table with the idea of creating user tiers (Admin, Registered, VIP, etc.).
  • Users get roles assigned on the user_roles table.
  • Roles get scopes assigned on the role_scopes table.
  • Users get projects assigned on the user_projects table.

EXAMPLE DATA

Here you have a SQL to create the tables on a MySQL database and populate them with sample data:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`users` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`user_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`roles` (
  `role_id` INT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`role_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`projects`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`projects` (
  `project_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`project_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`user_projects`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`user_projects` (
  `user_id` INT NOT NULL,
  `project_id` INT NOT NULL,
  PRIMARY KEY (`user_id`, `project_id`),
  INDEX `fk_user_projects_projects1_idx` (`project_id` ASC),
  INDEX `fk_user_projects_users1_idx` (`user_id` ASC),
  CONSTRAINT `fk_user_projects_projects1`
    FOREIGN KEY (`project_id`)
    REFERENCES `mydb`.`projects` (`project_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_projects_users1`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`user_roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`user_roles` (
  `user_id` INT NOT NULL,
  `role_id` INT NOT NULL,
  PRIMARY KEY (`user_id`, `role_id`),
  INDEX `fk_user_roles_roles1_idx` (`role_id` ASC),
  INDEX `fk_user_roles_user_projects1_idx` (`user_id` ASC),
  CONSTRAINT `fk_user_roles_roles1`
    FOREIGN KEY (`role_id`)
    REFERENCES `mydb`.`roles` (`role_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_roles_user_projects1`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`user_projects` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`scopes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`scopes` (
  `scope_id` INT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(255) NULL,
  PRIMARY KEY (`scope_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`role_scopes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`role_scopes` (
  `role_id` INT NOT NULL,
  `scope_id` INT NOT NULL,
  PRIMARY KEY (`role_id`, `scope_id`),
  INDEX `fk_role_scopes_roles1_idx` (`role_id` ASC),
  INDEX `fk_role_scopes_scopes1_idx` (`scope_id` ASC),
  CONSTRAINT `fk_role_scopes_roles`
    FOREIGN KEY (`role_id`)
    REFERENCES `mydb`.`roles` (`role_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_role_scopes_scopes1`
    FOREIGN KEY (`scope_id`)
    REFERENCES `mydb`.`scopes` (`scope_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `mydb`.`users`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`users` (`user_id`, `name`) VALUES (1, 'admin');
INSERT INTO `mydb`.`users` (`user_id`, `name`) VALUES (2, 'user1');
INSERT INTO `mydb`.`users` (`user_id`, `name`) VALUES (3, 'user2');

COMMIT;


-- -----------------------------------------------------
-- Data for table `mydb`.`roles`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`roles` (`role_id`, `code`) VALUES (1, 'ADMIN');
INSERT INTO `mydb`.`roles` (`role_id`, `code`) VALUES (2, 'REGISTERED');

COMMIT;


-- -----------------------------------------------------
-- Data for table `mydb`.`projects`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`projects` (`project_id`, `name`) VALUES (1, 'Project 1');
INSERT INTO `mydb`.`projects` (`project_id`, `name`) VALUES (2, 'Project 2');
INSERT INTO `mydb`.`projects` (`project_id`, `name`) VALUES (3, 'Project 3');

COMMIT;


-- -----------------------------------------------------
-- Data for table `mydb`.`user_projects`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`user_projects` (`user_id`, `project_id`) VALUES (1, 1);
INSERT INTO `mydb`.`user_projects` (`user_id`, `project_id`) VALUES (1, 2);
INSERT INTO `mydb`.`user_projects` (`user_id`, `project_id`) VALUES (1, 3);
INSERT INTO `mydb`.`user_projects` (`user_id`, `project_id`) VALUES (2, 1);
INSERT INTO `mydb`.`user_projects` (`user_id`, `project_id`) VALUES (2, 3);
INSERT INTO `mydb`.`user_projects` (`user_id`, `project_id`) VALUES (3, 2);
INSERT INTO `mydb`.`user_projects` (`user_id`, `project_id`) VALUES (3, 3);

COMMIT;


-- -----------------------------------------------------
-- Data for table `mydb`.`user_roles`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`user_roles` (`user_id`, `role_id`) VALUES (1, 1);
INSERT INTO `mydb`.`user_roles` (`user_id`, `role_id`) VALUES (2, 2);
INSERT INTO `mydb`.`user_roles` (`user_id`, `role_id`) VALUES (3, 2);

COMMIT;


-- -----------------------------------------------------
-- Data for table `mydb`.`scopes`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`scopes` (`scope_id`, `code`) VALUES (1, 'project:view');
INSERT INTO `mydb`.`scopes` (`scope_id`, `code`) VALUES (2, 'project:edit');
INSERT INTO `mydb`.`scopes` (`scope_id`, `code`) VALUES (3, 'project:delete');
INSERT INTO `mydb`.`scopes` (`scope_id`, `code`) VALUES (4, 'user:edit');
INSERT INTO `mydb`.`scopes` (`scope_id`, `code`) VALUES (5, 'user:create');
INSERT INTO `mydb`.`scopes` (`scope_id`, `code`) VALUES (6, 'user:delete');

COMMIT;


-- -----------------------------------------------------
-- Data for table `mydb`.`role_scopes`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`role_scopes` (`role_id`, `scope_id`) VALUES (1, 1);
INSERT INTO `mydb`.`role_scopes` (`role_id`, `scope_id`) VALUES (1, 2);
INSERT INTO `mydb`.`role_scopes` (`role_id`, `scope_id`) VALUES (1, 3);
INSERT INTO `mydb`.`role_scopes` (`role_id`, `scope_id`) VALUES (1, 4);
INSERT INTO `mydb`.`role_scopes` (`role_id`, `scope_id`) VALUES (1, 5);
INSERT INTO `mydb`.`role_scopes` (`role_id`, `scope_id`) VALUES (1, 6);
INSERT INTO `mydb`.`role_scopes` (`role_id`, `scope_id`) VALUES (2, 4);

COMMIT;

PROBLEM

I need to limit what users can do within each project. A user can be in 2 projects and have different scopes on each one of them. And 10 users can be on the same project, but not necessarily have the same scopes for that project.

I can think of the following ways to do this:

  • Create a project_permissions table that will store a user_id and a scope_id.
  • Create a groups table, create a project_groups table and assign scopes via a group_scopes table. Then, via a user_groups table, assign users to the groups.

I want to do this in such a way that in the future I can create different 'objects' (a project would be an example of an 'object' in my app) and assign scopes to the users assigned to those 'objects' (same way I want to assign scopes to the projects).

QUESTION

What would be the best option to implement? Is there a better way (maybe even simpler) to do this?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.