SQL of tables with multiple relationships

Question

• How would be the SQL representations of creation and correct insertion of the tables below so that it is possible to have/store the total number of videos of a genre so that it is possible to take, for example, all movies of the user 1 that are of the genre 3?
• Select films of a particular genre (the genre is passed by name and not by id).

CREATE TABLE usuario(
id INTEGER NOT NULL AUTO_INCREMENT,
email VARCHAR(50) NOT NULL UNIQUE,
nome VARCHAR(40) NOT NULL,
data_nascimento DATE NOT NULL,
PRIMARY KEY(id));

CREATE TABLE filme(
id INTEGER NOT NULL AUTO_INCREMENT,
nome VARCHAR(40) NOT NULL,
duracao INT NOT NULL,
PRIMARY KEY(id));

CREATE TABLE genero(
id INTEGER NOT NULL AUTO_INCREMENT,
nome VARCHAR(40) NOT NULL,
PRIMARY KEY(id));

CREATE TABLE rel_usuario_filme( 
id_usuario INTEGER NOT NULL, 
id_filme INTEGER NOT NULL, 
PRIMARY KEY(id_usuario, id_filme), 
CONSTRAINT fk_usuario FOREIGN KEY (id_usuario) REFERENCES usuario(id), 
CONSTRAINT fk_filme FOREIGN KEY (id_filme) REFERENCES filme(id));

CREATE TABLE rel_filme_genero(
id_filme INTEGER NOT NULL,
id_genero INTEGER NOT NULL,
PRIMARY KEY(id_filme, id_genero),
CONSTRAINT fk_genero FOREIGN KEY (id_genero) REFERENCES genero(id),
CONSTRAINT fk_filme FOREIGN KEY (id_filme) REFERENCES filme(id));

Answer 1

Diagram

What I understand about your problem:

It would be correct to say then that

• 1 user may have 1 or N films
• 1 film may have 1 or N generous

it is possible to read backwards also

• 1 gender may be in N films
• 1 movie may be with N users. Actually I think you meant that 1 film can be with only 1 user, in this case, your business rule will have to guarantee this state.

Asking questions for the data model

How to count the number of films of a particular genre ?

SELECT g.nome, (SELECT COUNT(*) FROM rel_filme_genero WHERE id_genero = g.id_genero) AS quantidade 
FROM genero g 
WHERE g.id_genero = 1 OR g.nome = "nomeDoGenero"

How to count the number of user movies of a given genre ?

SELECT usuario.nome, genero.nome, COUNT(*) AS quantidade FROM usuario 
LEFT JOIN rel_usuario_filme ON usuario.id_usuario = rel_usuario_filme.id_usuario 
LEFT JOIN filme ON rel_usuario_filme.id_filme = filme.id_filme 
LEFT JOIN rel_filme_genero ON filme.id_filme = rel_filme_genero.id_filme 
LEFT JOIN genero ON rel_filme_genero.id_genero = genero.id_genero 
WHERE usuario.id_usuario = 1 AND genero.nome = "Acao" 
GROUP BY usuario.id_usuario, genero.id_genero

How I discover the amount of movies by genre?

Within the various possible forms, follows a:

SELECT nome, (SELECT COUNT(*) FROM rel_filme_genero WHERE id_genero = g.id_genero) AS quantidade 
FROM genero g

You can use this simple data template I made in SQL Fiddle to test

Taking advantage, you can exchange LEFT (which will always give preference for the records on the left) INNER to only return result lines home there are records on both sides at the time of performing the merge. In this question, you have more details about the types of JOIN’s

Answer 2

The structure you have right now allows you to answer your two questions:

• "store the total number of videos of a genre in such a way that it is possible to take, for example, all the movies of user 1 that are of genre 3?"

Number of films for each genre:

select g.nome, count(rel.id_filme)
from genero g
left join rel_filme_genero rel
   on rel.id_genero = g.id
group by g.nome

All user 1 movies that are type 3:

select f.nome
from filmes f
inner join rel_filme_usuario relfu
  on relfu.id_filme = f.id
inner join rel_filme_genero relfg
  on relfg.id_filme = f.id
where relfu.id_usuario = 1
  and relgf.id_genero = 3

"Select films of a particular genre (the genre is passed by name and not by id)":

select g.nome, f.nome, f.duracao
from genero g
inner join rel_filme_genero relfg
   on relfg.id_genero = g.id
inner join filmes f
   on f.id = relfg.id_filme
where g.nome = 'Comedia'

Insert the movie and then the genre information. I would execute this in a transaction:

start transaction

insert into filme (nome, duracao) 
values ('Titanic', 120);
set @filme_id = LAST_INSERT_ID();
insert into rel_filme_genero (id_filme, id_genero) 
select @filme_id, g.id 
from generos g
where g.nome in ('comedia', 'romance'); 

commit

I simply suggest that in your process flow you always ensure that the content of the Gender table is controlled by you and do not allow indiscriminate input of values. Because the universe of values for Gender is not very big. This way I believe you can keep the current table structure.

Answer 3

To fix the problem of relationship I will pass your script with the necessary changes, have a comment already explaining why the change

DROP TABLE IF EXISTS usuario;
CREATE TABLE usuario(
id INTEGER NOT NULL AUTO_INCREMENT,
email VARCHAR(50) NOT NULL UNIQUE,
nome VARCHAR(40) NOT NULL,
data_nascimento DATE NOT NULL,
PRIMARY KEY(id));

DROP TABLE IF EXISTS filme;
CREATE TABLE filme(
id INTEGER NOT NULL AUTO_INCREMENT,
nome VARCHAR(40) NOT NULL,
duracao INT NOT NULL,
PRIMARY KEY(id));

DROP TABLE IF EXISTS genero;
CREATE TABLE genero(
id INTEGER NOT NULL AUTO_INCREMENT,
nome VARCHAR(40) NOT NULL,
PRIMARY KEY(id));

DROP TABLE IF EXISTS rel_usuario_filme;
CREATE TABLE rel_usuario_filme( 
id_usuario INTEGER NOT NULL, 
id_filme INTEGER NOT NULL, 
PRIMARY KEY(id_usuario, id_filme), 
CONSTRAINT fk_usuario FOREIGN KEY (id_usuario) REFERENCES usuario(id), 
CONSTRAINT fk_filme FOREIGN KEY (id_filme) REFERENCES filme(id));

DROP TABLE IF EXISTS rel_filme_genero;
CREATE TABLE rel_filme_genero(
id_filme INTEGER NOT NULL,
id_genero INTEGER NOT NULL,
PRIMARY KEY(id_filme, id_genero),
CONSTRAINT fk_rel_fg_genero FOREIGN KEY (id_genero) REFERENCES genero(id),
CONSTRAINT fk_rel_fg_filme FOREIGN KEY (id_filme) REFERENCES filme(id));

ALTER TABLE `test`.`rel_filme_genero` 
DROP FOREIGN KEY `fk_rel_fg_filme`;
ALTER TABLE `test`.`rel_filme_genero` 
ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT COMMENT 'id_filme e id_genero nao pode ser chave composta pois 1 filme pode ser comedia romantica por exemplo, entao as duas sao apenas chaves estrangeiras aqui' FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`),
ADD INDEX `fk_rel_fg_filme_idx` (`id_filme` ASC);
ALTER TABLE `test`.`rel_filme_genero` 
DROP FOREIGN KEY `fk_rel_fg_genero`;
ALTER TABLE `test`.`rel_filme_genero` ADD CONSTRAINT `fk_rel_fg_genero`
  FOREIGN KEY (`id_genero`)
  REFERENCES `test`.`genero` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_rel_fg_filme`
  FOREIGN KEY (`id_filme`)
  REFERENCES `test`.`filme` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

For your question about the total of films user 1 that is of gender 3, the rule of the normal form says not to store information that can be obtained through calculation and/or query ( do not remember the rule number and possibly not with these words ) then the correct one would be a query that makes that total p/ you

SELECT u.nome,
    (select sum(1) from rel_usuario_filme ruf, rel_filme_genero rfg where ruf.id_usuario=u.id and ruf.id_filme=rfg.id_filme and rfg.id_genero=3) as total_filmes
FROM usuario u
where 
    -- u.nome = 'joao' ou pelo id abaixo
    u.id = 1;