How to make a relationship between 5 tables?

Navigation
0

I have the following tables: conteúdo , aulas , curso , turma , grupo_User where the logic will be as follows:

  • A content can belong to several classes, as a lesson can have several contents
  • A class can belong to several courses, just as a course can have several classes.
  • A course can belong to several classes, just as a class can have several courses to enroll.
  • A group of users can be linked to different groups, just as one group can receive different groups of users.

    • If I am right, these relationships are all N: N , I also know that one more table is needed to bind the tables.

    Below I'll leave the structure of the tables:

    Content: 

    CREATE TABLE conteudo (
    id INT(12) PRIMARY KEY,
    autor VARCHAR(50),
    conteudo MEDIUMTEXT,
    data_criacao DATETIME,
    ultima_edicao VARCHAR(50),
    data_edicao DATETIME
);

    Classrooms: 

    CREATE TABLE aulas (
    id INT(12) PRIMARY KEY,
    autor VARCHAR(50),
    nome VARCHAR(50),
    data_criacao DATETIME,
    ultima_edicao VARCHAR(50),
    data_edicao DATETIME
);

    Course: 

    CREATE TABLE curso (
    id INT(12) PRIMARY KEY,
    autor VARCHAR(50),
    nome VARCHAR(50),
    data_criacao DATETIME,
    ultima_edicao VARCHAR(50),
    data_edicao DATETIME
)

    Classes: 

    CREATE TABLE turmas(
    id INT(12) PRIMARY KEY,
    autor VARCHAR(50),
    nome VARCHAR(50),
    data_criacao DATETIME,
    ultima_edicao VARCHAR(50),
    data_edicao DATETIME
)

    Grupo_User: 

    CREATE TABLE grupo_User(
    id INT(12) PRIMARY KEY,   
    nome_Grupo VARCHAR(50),
    id_Users INT(20)
)

    I even managed to link the content to classes using the following table: 

    CREATE TABLE aula_has_conteudo (
    idAula INT(12),
    idConteudo INT(12),
    CONSTRAINT PK_aulas_has_conteudo PRIMARY KEY (idAula, idConteudo),
    CONSTRAINT FK_aulas_has_conteudo_aula FOREIGN KEY (idAula) REFERENCES aulas (id),
    CONSTRAINT FK_aulas_has_conteudo_conteudo FOREIGN KEY (idConteudo) REFERENCES conteudo (id)
);

    Using the following SELECT I can get the data you want: 

    SELECT  a.autor as autorAula, c.conteudo as conteudo FROM aulas a INNER JOIN aula_has_conteudo h ON a.id = h.idAula 
INNER JOIN conteudo c ON c.id = h.idConteudo

    And I was able to bind the aulas to the cursos using the following table: 

    CREATE TABLE curso_has_aula (
    idCurso INT(12),
    idAula INT(12),
    CONSTRAINT PK_curso_has_aula PRIMARY KEY (idCurso, idAula),
    CONSTRAINT FK_curso_has_aula_Curso FOREIGN KEY (idCurso) REFERENCES curso (id),
    CONSTRAINT FK_curso_has_aula_Aula FOREIGN KEY (idAula) REFERENCES aulas (id)
)

    But I can not connect between the other tables.

    How can I relate all of these tables knowing the above requirements?

    After related, how can I make a select to get data from the five tables?

        
    asked by anonymous 11.07.2018 / 19:36

    1 answer

    2

    Just keep doing the joins with the other tables, it will be great but just go adding the other tables: 

    SELECT a.autor as autorAula, c.conteudo as conteudo 
  FROM aulas a 
 INNER JOIN aula_has_conteudo h ON a.id = h.idAula 
 INNER JOIN conteudo c ON c.id = h.idConteudo
 INNER JOIN curso_has_aula cha on a.id = cha.idAula
 INNER JOIN curso cr on cr.id = cha.idCurso
 -- e assim por diante
        
    11.07.2018 / 19:56
    How to transform a list into set in Python? Create multiple PHP variables in the same function