Relationship (0.1) in mysql Workbench

Question

I’m doing a project and I started by modeling the bank was all right up to the point of users that I got confused.

Rules: general admin (access to all), admin company(access to all company events) and the user (access to only one event). In the users table I want to leave:

1. user A associated with a company.
2. user B not being associated with any company. (administrators)
3. user C be associated with a company and an event.

in this case I need to have a foreign key with the table enterprise and event. But this way I’m stuck to always associate a admin for a company and an event. How to resolve this by removing the keys?

Answer 1

I will make the associative between the user table and the company table and you will see that the same case for the event table.

First I’ll put the example code

CREATE TABLE `teste`.`tab_usuario` (
`usu_id` INT NOT NULL AUTO_INCREMENT COMMENT '',
`nome` VARCHAR(45) NULL COMMENT '',
PRIMARY KEY (`usu_id`)  COMMENT '');

CREATE TABLE `teste`.`tab_empresa` (
`emp_id` INT NOT NULL AUTO_INCREMENT COMMENT '',
`emp_nome` VARCHAR(45) NULL COMMENT '',
PRIMARY KEY (`emp_id`)  COMMENT '');

INSERT INTO `teste`.`tab_empresa` (`emp_id`, `emp_nome`) VALUES ('1', 'empresa1');
INSERT INTO `teste`.`tab_empresa` (`emp_id`, `emp_nome`) VALUES ('2', 'empresa2');

INSERT INTO `teste`.`tab_usuario` (`usu_id`, `nome`) VALUES ('1', 'john');
INSERT INTO `teste`.`tab_usuario` (`usu_id`, `nome`) VALUES ('2', 'noah');

CREATE TABLE `teste`.`rel_usuario_empresa` (
`usu_id` INT NOT NULL COMMENT '',
`emp_id` INT NOT NULL COMMENT '',
PRIMARY KEY (`usu_id`, `emp_id`)  COMMENT '',
UNIQUE INDEX `usu_id_UNIQUE` (`usu_id` ASC)  COMMENT '');

This associative table rel_usuario_empresa this recital that 1 user will be unique UNIQUE INDEX usu_id_UNIQUE to ensure that the user registered there is not duplicated or inserted in two companies.

INSERT INTO `teste`.`rel_usuario_empresa` (`usu_id`, `emp_id`) VALUES ('1', '1');

And to make a query I leave the example below:

select
    u.nome, -- nome na tab_usuario
    e.emp_nome -- nome da empresa tab_empresa
from tab_usuario u, rel_usuario_empresa r, tab_empresa e -- tabelas a serem consultadas
where   u.usu_id=1  -- filtro do usuario codigo 1
        and u.usu_id = r.usu_id -- relacao entre tab_usuario e rel_usuario_empresa atraves
        and r.emp_id = e.emp_id -- relacao entre rel_usuario_empresa e tab_empresa

Under the terms john is associated with company1 as Noah not associated with any company, if you try to insert john again in rel_usuario_company with the same company or another company that is the relationship will already ensure that this does not happen.

user C would be the same problem so it would create another associative, and register it both in tab_empresa and in tab_evento and their respective associative tables.