Ayuda mostrar filas se relacion muchos a muchos

[Filantropo]

Hola una ayuda.
Tengo que registrar las personas que asisten a reuniones, en cada reunion pueden haber muchos participantes, cada participante puede participar en mas de una reunion, cada participante tiene un papel o rol en cada reunion que son "juez, acusado o demandante", este rol varia en cada reunion, un participante puede ser juez en una reunion y acusado o demandante en otra, el numero de acusados o demandantes puede ser distinto en cada reunion
Lo que tengo hecho:

-- relacion muchos a muchod entre reunion y participantes, en la tabla intermedia se guarda el rol que cumplia el participante en esa reunion

Table_Reunion    |   Table_intermedia         |    Table_participante
                             |                                           |
     Id_reunion     |         Id_reunion                |         Id_participante
     Fecha             |          Id_participante       |          nombres
                             |          rol_partocipante    |



Lo que necesito es mostrar 4 columnas con los datos de fecha_reunion, juez, demandante, acusado:

fecha	juez	demandante	acusado
2019/05/09	juan	maria	melissa
		antonieta	jose
		maria	leydi
2020/09/08	antonio	susana	jose
		maria
		melinda
2020/09/01	matias	jair	sol
			jose
			juan

Que sentencia podria mostrar los datos de esa forma?

Edicion
Las participantes sean jueces, demandantes o acusados se guardan en una unica columna de la tabla intermedia (idparticipante), como podria mostrarlo en una columna propia?


Edicion 2
O de que otra manera se podria descomponer el problema o las tablas?

[WHK]

Hay algunos detalles en tu modelo, por ejemplo, que tipo de dato es "rol_partocipante" ?, si es un varchar, que pasa si alguien ingresa el valor con un punto al final o un espacio en blanco o con mayusculas? duplicarás los registros para un mismo tipo?, no sería mejor contar con una tabla para roles y que solo se asocie el id del rol?, a demás, porque las tablas dicen "Table_" al comienzo si se sabe que son tablas? es como si las columnas comenzaran con "Column_", lo mismo sucede con "Id_reunion", si se sabe que la tabla es reunion, porque la columna tambien se llama reunion?. También te recomiendo que no uses nombres tan genéricos, por ejemplo, que la tabla "intermedia" se llame por ejemplo "reuniones_participantes", porque sino, que harás si tienes mas de una tabla intermedia?.r

Por otro lado, SQL retorna filas, no un arbol de datos, por lo cual no puedes esperar a que la primera fila contenga ciertos datos y en otras otros datos y para obtener tu consulta sql debes hacer un join:

Código [Seleccionar] Expandir

SELECT
    reunion.fecha,
    participantes.id
FROM reunion
INNER JOIN reuniones_participantes ON (reuniones_participantes.reunion_id = reunion.id)
INNER JOIN participantes ON (participantes.id = reuniones_participantes.participante_id)
WHERE reunion.id

Pero como no me dices mucho sobre lo que contiene la columna rol, puedo asumir una tabla de roles de la siguiente manera:

Código [Seleccionar] Expandir

SELECT
    reunion.fecha,
    participantes.id,
    participantes.nombre,
    roles.id,
    roles.name
FROM reunion
INNER JOIN reuniones_participantes ON (reuniones_participantes.reunion_id = reunion.id)
INNER JOIN participantes ON (participantes.id = reuniones_participantes.participante_id)
INNER JOIN roles ON (roles.id = reuniones_participantes.rol_id)
WHERE reunion.id

De esta manera obtendrás una lista de todos los integrantes con sus roles y fechas de reunión a traves de un id de reunión.

Ahora, no te recomiendo hacer esto porque, primero, es desordenado y no es eficiente ya que estás intentando crear un retorno de lógica utilizando una base de datos que no fue diseñado para ello, a demás, es una pésima práctica obtener todod e una única ves ya que desperdicias recursos e impides la escalabilidad de tu aplicación porque ya no podrás separar cada dato cuando necesites hacer un paginador, por ejemplo.

Te recomiendo que desde tu aplicación hagas uso de modelos de datos. Si quieres obtener un listado de reuniones entonces no necesitas todos los datos, si necesitas ver el detalle de una reunión necesitarás otros datos con mas detalles y para ello debieras realizar mas de una consulta sql utilizando varios modelos de datos, esto evita por ejemplo que la base de datos tenga que retornar multiples filas con el mismo registro (como la fecha por ejemplo).

En el caso de querer listar reuniones de la manera que lo planteas es porque necesitas procesar los datos de manera individual y desde tu código del aplicativo agrupar los resultados y mostrar por ejemplo un único nombre de reunión, una única fecha y abajo los asistentes, para esto puedes realizar una consulta sql con un listado finito (usando LIMIT y ORDER BY) de reuniones y al ir mostrando una por una vas a ir buscando sus ntegrantes con sus roles asociados a traves de un modelo de datos que lo haga de manera autónoma y con contenido limitado (sólo nombres por ejemplo). Esto te permitirá escalar tu sección de listado de reuniones, por ejemplo, cuando quieras integrar un buscador o un paginador de resultados.

También te recomiendo desnormalizar, eos quiere decir que puedes tener una tabla, por ejemplo, llamada "reuniones_desnormalizado" donde contenga el id de la reunion, el nombre y fecha de la reunion y los nombres de los integrantes en formato texto separado por comas, luego en el listados olo muestras el valor de la columna tal cual y ya, ya que no necesitarás el id del usuario porque no habrá un enlace hacia el perfil del usuario o una imagen con su rostro (supongo), eso es optimizar los recursos y escalar la aplicación.

Saludos.

[Filantropo]

Pero como no me dices mucho sobre lo que contiene la columna rol, puedo asumir una tabla de roles de la siguiente manera:

Olvide decir que que el rol_participante es el id de una tabla de los roles.

Te recomiendo que desde tu aplicación hagas uso de modelos de datos. Si quieres obtener un listado de reuniones entonces no necesitas todos los datos, si necesitas ver el detalle de una reunión necesitarás otros datos con mas detalles y para ello debieras realizar mas de una consulta sql utilizando varios modelos de datos, esto evita por ejemplo que la base de datos tenga que retornar multiples filas con el mismo registro (como la fecha por ejemplo).

¿modelos de datos desde aplicacion? ¿que es eso?. Googlee y me sale modelado de bases de datos, cardinalidad, relaciones etc, como los busco el tema?

Esto te permitirá escalar tu sección de listado de reuniones, por ejemplo, cuando quieras integrar un buscador o un paginador de resultados.

¿que es Escalar?

También te recomiendo desnormalizar, eos quiere decir que puedes tener una tabla, por ejemplo, llamada "reuniones_desnormalizado" donde contenga el id de la reunion, el nombre y fecha de la reunion y los nombres de los integrantes en formato texto separado por comas, luego en el listados olo muestras el valor de la columna tal cual y ya, ya que no necesitarás el id del usuario porque no habrá un enlace hacia el perfil del usuario o una imagen con su rostro (supongo), eso es optimizar los recursos y escalar la aplicación.

Esa me parece la solucion mas facil de aplicar, pero una duda, la tabla reuniones_desnormalizada tendria practicamente los mismos datos que la tabla intermedia reuniones_participantes pero en forma de texto entonces serian datos redundantes ademas que ocuparian mucho mas peso la base de datos, esto podria generar algun problema a futuro? Lo digo porque de los tutoriales de bases de datos vistos se insiste en la normalizacion y asi evitarse problemas de inconsistencia, redundancia, lentitud y otras cosas. Estoy confundido 

Estoy verde en estos temas.

[WHK]

Estoy verde en estos temas.

Tranquilo, nadie nace sabiendo las cosas.

¿modelos de datos desde aplicacion?

Un modelo de datos es cuando creas una clase (objeto en tu lenguaje de programación) y ese refleja los datos que están en la base de datos, por ejemplo, si tienes una tabla llamada reuniones con dos columnas entonces tendras una clase llamada reuniones con dos variables y los valores se obtendrán desde la base de datos, de esa manera tu código de lógica (capa de negocio o servicio) no tendrá porque ir a consultar a una base de datos y tendrás integridad.

Algo así: https://www.iteramos.com/pregunta/21106/diseno-del-modelo-de-repositorio-adecuado-en-php

No se aun en que lenguaje estás programando pero deberías usar un framework, estos frameworks hacen todo el trabajo y no debes estar haciendo las consultas sql manualmente. Para cada lenguaje de programación hay frameworks, eso dependerá si tu app es de escritorio o web, por ejemplo laravel, codeigniter, spring bot, express, etc.

¿que es Escalar?

Es simplemente cuando tu aplicación está diseñada para ser ampliada sin dar problemas, por ejemplo, supongamos que por ahora solo quieres hacer un listado de reuniones, pero talves en algun momento necesitarás agregarle mas cosas, por ejemplo lugares, digamos que un mismo lugar pueda ser agendado por mas de una fecha, entonces necesitarás una tabla de lugares y separar las busquedas por id de reunion y por lugar, despues talves necesitarás gestionar los lugares por dependencias (edificios), talves despues necesitarás llevar una estadística de cuantas reuniones ha tenido un unico usuario y necesitarás hacer una consulta sql muy rápida, porque si crece y llegas a tener miles de registros puede que tus informes estadísticos tarden muchos minutos en ejecutarse y mientras tanto bloquearás la base de datos por exceso de carga o bloqueo nativo. Pensar en el futuro de la aplicación y crear una buena estructura desde el principio es hacer una aplicación escalable.

Lo digo porque de los tutoriales de bases de datos vistos se insiste en la normalizacion y asi evitarse problemas de inconsistencia, redundancia

La técnica de desnormalización se utiliza mucho, de hecho es un tipo de estructura y acceso a la información, crear datos redundantes te ayuda a alivianar la carga de las consultas sql siempre y cuando mantengas la integridad desde tu aplicación, esto quiere decir lo siguiente:

Las dos maneras más comunes de crear una aplicación es de manera transaccional y/o analítica, transaccional significa que usarás transacciones, o sea, consultas sql, inserciones y actualizaciones y nada mas, todas las tablas son únicas y cada tabla tiene su relación, a esto se le llama una base de datos relacional, pero cuando creas una base de datos analítica necesitas crear redundancia y particionamiento de datos, a esto se le llama desnormalización y sirve cuando necesitas crear reportes con muchos datos unidos, por ejemplo un informe total por més de la cantidad total de reuniones en % de totalidad entre las audiencias que tuvieron éxito o no, un % de inasistencias, un top de usuarios que no asistieron, un top de salas más usadas por día de la semana y esas cosas, imagina si necesitas todos esos datos en un único informe y necesitas ir a consultar a cientos de miles de registros o talves millones, probablemente hacer semejante cantidad de consultas requiera ir a recorrer todos los registros de la base de datos, hacer ordenamientos inversos (para hacer el top), etc, probablemente tardes una hora o más en sacar un solo informe, ahora piensa que mientras la aplicación está ejecutando todas estas consultas sql t base de datos quedará inoperativa y puede que mas de un usuario necesite el mismo informe más de una ves al día, entonces una base de datos transaccional normalizada no sirve y tienes varias opciones, la mayoría de ellas costosas y de compleja implementación, por ejemplo utilizando bigdata, apache hadoop, apache solr, etc, pero la manera mas simple y al alcance de un usuario normal es hacer desnormalización, o sea, duplicar datos.

¿Cómo se mantiene esto en integridad?: Cada ves que tu aplicación inserte, elimine o modifique un registro tendrás que actualizar el contador o registro redundante manualmente en la tabla desnormalizada.

Un ejemplo práctico es este mismo foro, está sobre una base llamada SMF construido en php y si te fijas, cuando ingresas la la pagina principal te dice muchas cosas, por ejemplo, cuantos mensajes personales tienes, te marca en gris los foros que no tienes nuevos temas que leer, dice la cantidad total de mensajes, la cantidad total de usuarios registrados, etc etc y no creas que el foro hace un calculo por cada usuario que ingresa al foro, sería una locura, por eso existe una tabla en la base de datos con toda esa información donde se guarda un contador total de temas, asi cuando se crea un tema se suma un uno a ese valor y listo y para saber los temas que no has leido lo saca de una tabla desnormalizada, esto quiere decir que smf duplica el titulo de cada post en diferentes tablas para no tener que irlas a buscar una por una y estar calculando los resultados, eso sería desastrozo si tienes miles de usuarios conectados simultaneamente leyendo el foro.

Es normal que la base de datos sea mas pesada en espacio pero será mas liviana en uso de memoria ram y cpu, tu elijes, que te sobra más? un par de gigas en el disco duro o teras de memoria ram?

Mira, aver si te sirve, lo hize en mysql:

Código [Seleccionar] Expandir

CREATE TABLE `lugares` (
   `id` INT NOT NULL,
   `nombre` VARCHAR(24) NOT NULL,
   PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

CREATE TABLE `reuniones` (
   `id` BIGINT NOT NULL,
   `fecha_inicio` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `fecha_termino` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `lugar_id` INT NOT NULL,
   PRIMARY KEY (`id`),
   INDEX `fk_reuniones_lugares1_idx` (`lugar_id` ASC),
   CONSTRAINT `fk_reuniones_lugares1`
     FOREIGN KEY (`lugar_id`)
     REFERENCES `lugares` (`id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE
)
ENGINE = InnoDB;

CREATE TABLE `personas` (
   `id` BIGINT NOT NULL,
   `nombres` VARCHAR(24) NOT NULL,
   `apellidos` VARCHAR(24) NOT NULL,
   PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

CREATE TABLE `roles` (
   `id` INT NOT NULL,
   `nombre` VARCHAR(24) NOT NULL,
   PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

CREATE TABLE `participantes` (
   `id` BIGINT NOT NULL,
   `reunion_id` BIGINT NOT NULL,
   `persona_id` BIGINT NOT NULL,
   `rol_id` INT NOT NULL,
   PRIMARY KEY (`id`),
   INDEX `fk_participantes_personas_idx` (`persona_id` ASC),
   INDEX `fk_participantes_roles1_idx` (`rol_id` ASC),
   INDEX `fk_participantes_reuniones1_idx` (`reunion_id` ASC),
   CONSTRAINT `fk_participantes_personas`
       FOREIGN KEY (`persona_id`)
       REFERENCES `personas` (`id`)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
   CONSTRAINT `fk_participantes_roles1`
       FOREIGN KEY (`rol_id`)
       REFERENCES `roles` (`id`)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
   CONSTRAINT `fk_participantes_reuniones1`
       FOREIGN KEY (`reunion_id`)
       REFERENCES `reuniones` (`id`)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION
)
ENGINE = InnoDB;

Para obtener el listado de reuniones lo haces así:

Código [Seleccionar] Expandir

SELECT
    lugares.nombre     as lugar_nombre     ,
    reuniones.fecha_inicio                 ,
    reuniones.fecha_termino                ,
    personas.nombres   as persona_nombre   ,
    personas.apellidos as persona_apellidos,
    roles.nombre       as rol_nombre
FROM reuniones
INNER JOIN lugares       ON ( lugares.id               = reuniones.lugar_id       )
INNER JOIN participantes ON ( participantes.reunion_id = reuniones.id             )
INNER JOIN roles         ON ( roles.id                 = participantes.rol_id     )
INNER JOIN personas      ON ( personas.id              = participantes.persona_id )
WHERE
    reuniones.id = 1
LIMIT 1;

saludos.

[Filantropo]

Hola.
Gracias por tomarte el tiempo de explicarme estos temas que en verdad no tenia ni idea, los ejemplos estan clarisimos, lo entendi todo, el modelo de datos, la integridad, la importancia de tablas desnormalizadas, yo creia que las aplicaciones de web hacian un simple inner join para tomar los datos de muchas tablas y no entendia pues como le hace para acceder a miles de registros de muchas tablas y devuelva resultados tan rapido 

Me encuentro haciendo una aplicacion en visual.net con su bd sqlserver y otra aplicacion en php , al principio lo que hacia colocar las sentencias sql directamente en la aplicacion y funcionaba, pero googleando y consultando di con que mejor es usar los procedimientos almacenados, el codigo sql y codigo vb estaba mezclado practicamente era un arroz con mango y tuve que rehacerlo, despues di con el tema de usuarios de bd, roles, login esquemas y pues ya van como 3 veces que reinicio  ahora con lo de las tablas desnormalizadas tendre que reiniciar otra vez pero a cambio aprendo mas.
Gracias.
Saludos.

[WHK]

y consultando di con que mejor es usar los procedimientos almacenados

Nopues, mejor no vuelvas a consultar más en ese sitio web xD, lo peor que puedes hacer es usar procedimientos almacenados porque eso aumenta la carga y pierdes integridad cuando necesitas escalar a más de una capa de negocio (o sea, tener a más de una aplicación usando la misma base de datos). Toda la lógica se debe hacer de lado de .net, no del motor de la base de datos, recuerda que el motor de la db es lo más pesado que se ejecuta en una aplicación, si tienes miles de visitas tu IIS soportará muy bien pero puede que tu base de datos no y tendrás que gastar mucho dinero en memorias ram y en hardware para hacer balanceo de carga. Mejor ahorrate todo eso y haz que la base de datos trabaje lo menos posible.

Ya que me has dicho que estás usando .net, averigua que es MVC5 para .net, persistencia, entity framework, como crear entidades y multiproyecto  vía services references en una misma solución, con eso debiera bastar.

Desde tu solución debes crear dos proyectos, uno con tu sitio web y otro que sirva solo para recibir y enviar datos desde y hacia la db, entonces desde tu proyecto web le pedirás vía referencia de servicio al proyecto de capa de datos que requieres el listado de reuniones, por ejemplo, DataService.getAllMettingsByPage(1) y tu capa de datos (el proyecto que se conecta a la db) utilizara linq para poder acceder a las entidades de la base de datos a traves de migraciones (lo puedes buscar en visual studio).

Actualmente no necesitas realizar consultas manualmente a la bae de datos, .net lo hace por ti, solo debes crear clases que representen a tus tablas y .net a traves de migraciones creará automáticamente la base de datos y aplicará los cambios cuando cambien algun objeto. Esto te permitirá escalar debido a que en un futuro podrás cambiar de sql server a oracle sin tener que reescribir todas las consultas sql otraves, solo basta con cambiar el driver de conexión de mvc y aplicar las migraciones de la base de datos (con un solo comando) y listo.

Aprovechando que vas a hacer todo de cero, te recomiendo utilizar c#, es mas intuitivo al momento de utilizar lambda y linq para acceder a los repositorios de los datos. A demás en un futuro te será más facil aprender otros lenguajes como Java y frameworks como Spring ya que nadie a excepción de microsoft utiliza un lenguaje Basic para crear aplicaicones modernas y eso te puede provocar una dependencia al largo plazo bien grave ya que si pierdes tu trabajo no podrás buscar otro empleo que no sea en empresas que usen visual basic y por lo general ya van en desuso.

Saludos.

[Filantropo]

Nopues, mejor no vuelvas a consultar más en ese sitio web xD, lo peor que puedes hacer es usar procedimientos almacenados porque eso aumenta la carga y pierdes integridad cuando necesitas escalar a más de una capa de negocio (o sea, tener a más de una aplicación usando la misma base de datos). Toda la lógica se debe hacer de lado de .net, no del motor de la base de datos, recuerda que el motor de la db es lo más pesado que se ejecuta en una aplicación, si tienes miles de visitas tu IIS soportará muy bien pero puede que tu base de datos no y tendrás que gastar mucho dinero en memorias ram y en hardware para hacer balanceo de carga. Mejor ahorrate todo eso y haz que la base de datos trabaje lo menos posible.

Lo que vi que por motivos de seguridad era que era mala practica que la aplicacion use directamente las sentencias sql select, update, insert  etc etc algo asi:

Código [Seleccionar] Expandir


`codigo vb
Dim comando as new sqlcommand()
Comando.text = "select nombres, apellidos from clientes"
Comando.executenonquery ()

En cambio se debia primero crear un user de bd al que se le revoquen todos los permisos de select, insert, update y solo pueda manipular las tablas a traves de proc almacenados, de esta maneracsolo podra ver solo los campo que le permita el proc almacenado.
Como dije estaba tuve que rehacer el trabajo que al prinxipio mezclaba sql+vb, esta vez uso los procs para el crud pero tambien los tendre que usar para validar usuarios, practicamente todo el trabajo lo tengo hecho en procs almacenados.

Desde tu solución debes crear dos proyectos, uno con tu sitio web y otro que sirva solo para recibir y enviar datos desde y hacia la db, entonces desde tu proyecto web le pedirás vía referencia de servicio al proyecto de capa de datos que requieres el listado de reuniones, por ejemplo, DataService.getAllMettingsByPage(1) y tu capa de datos (el proyecto que se conecta a la db) utilizara linq para poder acceder a las entidades de la base de datos a traves de migraciones (lo puedes buscar en visual studio).

Esto me suena a chino.

Aprovechando que vas a hacer todo de cero, te recomiendo utilizar c#, es mas intuitivo al momento de utilizar lambda y linq para acceder a los repositorios de los datos. A demás en un futuro te será más facil aprender otros lenguajes como Java y frameworks como Spring ya que nadie a excepción de microsoft utiliza un lenguaje Basic para crear aplicaicones modernas y eso te puede provocar una dependencia al largo plazo bien grave ya que si pierdes tu trabajo no podrás buscar otro empleo que no sea en empresas que usen visual basic y por lo general ya van en desuso.

¿en serio ya no vale el visual basic? La verdad no se cuales lenguajds son los mas requeridos por las empresas, me pidieron que haga una aplicacion no dijeron que lenguaje o hertamientaas usar y pues elegi lo ultimo que habia aprendido, lo bueno es que conozco un poco de java, no creo sea muy dificil aprender c#.

Bueno como ves, sin experiencia, aun estoy verde en estos temas, me queda buscar esos temas que señalas y aplicarlos en los prox. proyectos.
Gracias 

[WHK]

En cambio se debia primero crear un user de bd al que se le revoquen todos los permisos de select, insert, update y solo pueda manipular las tablas a traves de proc almacenados, de esta maneracsolo podra ver solo los campo que le permita el proc almacenado.
Como dije estaba tuve que rehacer el trabajo que al prinxipio mezclaba sql+vb, esta vez uso los procs para el crud pero tambien los tendre que usar para validar usuarios, practicamente todo el trabajo lo tengo hecho en procs almacenados.

como te dije antes, alejate de esos sitios webs. El que maneja la integridad de acceso debe ser la capa de datos desde la aplicación, no la base de datos. Si un día necesitas dar acceso a una aplicación que se va a conectar a la misma base de datos que harás? tendrás dos grandes problemas, primeramente tendrías que tener dos permisos distintos, uno para leer y otro para actualizar y vas a tener que usar usuarios distintos con procedimientos distintos, lo segundo es que tendrías que estarle haciendo cambios directamente a la base de datos sin pasar por el proceso de paso a producción de la aplicación porque no tendrás los cambios por escrito en código ni habrá una manera de devolver el estado anterior de la db si algo sale mal. He trabajado muchos años en muchas empresas que han utilizado procedimientos almacenados en .net y tienen los peores dolores de cabeza.

Se ordenado y haz las cosas bien desde el comienzo.

La verdad no se cuales lenguajds son los mas requeridos por las empresas

Actualmente el mas requerido a nivel mundial en el mundo laboral es Java. Dale un vistazo a esto:

https://computerhoy.com/noticias/tecnologia/estos-son-lenguajes-programacion-demandados-empresas-423369
http://www.cleformacion.com/-/los-10-lenguajes-de-programacion-mas-demandados-en-2018
https://www.campusmvp.es/recursos/post/tendencias-en-lenguajes-de-programacion-2019-la-encuesta-anual-de-stack-overflow.aspx

Como verás, a nivel laboral, visual basic no está en ningún top.

Saludos.