I'm doing a project and started by modeling the database was all right up to the point of users that I was confused.
Rules: general admin (access to all), admin company (access to all company events) and user (access to only one event). In the table users want to leave:
In this case, you need to have a foreign key with the company and event table. But that way I'm stuck always associating an admin for a company and an event. How do I resolve this by removing the keys?
I'm going to make the associative between the user table and the company table and see 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 is considering 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 an appointment 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
john is associated with company1 while noah is not associated with any company if you attempt to enter john again in the corporate_re_user with the same company or other company that is the relationship will already ensure that this does not happen.
user C would be the same problem then it would create another associative, and register it both on the company tab and on the event tab and their respective associative tables.