como establecer parametros de consulta variables

3l3azar · #1 (**permalink**) 10/10/2009, 05:23

Hola a todos! este es mi primer mensaje en el foro, y además acabo de empezar con esto de SQL, espero no estar equivocandome de topico, ni haciendo una pregunta absurda, ni nada por el estilo...
he estado buscando la respuesta a mi problema en otros posts pero no he encontrado nada que me sirva. mi problema es que tengo una tabla donde almaceno los horarios de los usuarios, tengo 7 campos, uno para cada dia de la semana, y en ellos almaceno las horas que los usuarios estan disponibles. lo que quiero hacer es una consulta que en funcion del dia de la semana busque la hora de la actividad en el campo correcto. he probado muchas cosas pero ninguna me funciona, me podriais ayudar?

esto no esta bien escrito, pero es para dar una idea y no se como explicarlo bien:

SELECT actividades.hora, CASE WHEN WEEKDAY(CURDATA())=1 THEN horario=perfiles.horarioLunes WHEN WEEKDAY(CURDATA()=2 THEN horario=perfiles.horarioMartes....
FROM actividades, perfiles
WHERE horario LIKE HOUR(actividades.hora)

supongo que se ve que estoy totalmente perdido :S. muchas gracias de antemano por la atencion

gnzsoloyo · #2 (**permalink**) 10/10/2009, 09:14

En realidad el problema es que estás tratando de resolver con una sentencia un proceso que debe hacerse en un Stored Procedure.
En todo caso, el problema es que la descripción de tu caso:

Cita:

lo que quiero hacer es una consulta que en funcion del dia de la semana busque la hora de la actividad en el campo correcto.

choca con un problema de diseño:

Cita:

tengo una tabla donde almaceno los horarios de los usuarios, tengo 7 campos, uno para cada dia de la semana, y en ellos almaceno las horas que los usuarios estan disponibles.

En realidad la tabla no debería tener 7 campos, sino en todo caso cuatro (usuario, {diaSemana, {HoraIni, HoraFin}}), de modo de poder realizar una consulta por los horarios que existen o no (más simple).
El modelo de datos que planteas requiere que tu consulta realice una comparación por columna, por lo menos. Lo que es muy ineficiente, ya que eso puede exigir incluso un JOIN séptuple...
Sería mejor que nos describieras la estructura de esas tablas para ver si hay una forma mejor.

3l3azar · #3 (**permalink**) 10/10/2009, 15:55

entiendo gnzsoloyo, muchas gracias por la respuesta. como acabo de empezar con esto todavia no entiendo muy bien las posibilidades del SQL.

veamos, fundamentalmente tengo dos tablas: actividades y usuarios. actividades tiene campos como categoria de la actividad, precio, dias de la semana que se realiza, horario, etc. la tabla usuarios tiene los campos usuario, precio maximo a pagar, categorias en las que esta interesado, y los siete que cite antes con los dias de la semana. esos siete campos son varchar y almacenan las horas que el usuario esta disponible (ej: 14,15,16,20,21,22).

de esta forma, yo comparo el precio de la actividad con el q el usuario esta dispuesto a pagar, para saber si le puedo ofrecer la actividad, hago otra comparacion con las categorias, y hasta ahi todo bien. el problema esta en el horario.

he pensado tu propuesta de los cuatro campos pero implicaria que para cada usuario seguro que habria mas de una linea. realmente no se si seria mas eficiente, estoy un poco perdido. me podriais dar alguna indicacion de por donde salir? estaba seguro de tener un error de diseño porq llevo ya un tiempo dandole vueltas al problema y no encontraba una solucion plausible.

muchas gracias por vuestra atencion y paciencia

gnzsoloyo · #4 (**permalink**) 11/10/2009, 08:45

Cita:

veamos, fundamentalmente tengo dos tablas: actividades y usuarios. actividades tiene campos como categoria de la actividad, precio, dias de la semana que se realiza, horario, etc. la tabla usuarios tiene los campos usuario, precio maximo a pagar, categorias en las que esta interesado, y los siete que cite antes con los dias de la semana. esos siete campos son varchar y almacenan las horas que el usuario esta disponible (ej: 14,15,16,20,21,22).

Bien, por lo que dices, está faltando un poco de análisis sobre la base del modelo Entidad-Relación (aquí tienes un PDF bastante didáctico que encontré en la web, si no has visto el tema), ya que cuando se aplica el modelo a tu caso, surgirán las siguientes restricciones (estoy poniendo algunas cosas que están implícitas por sistema, más allá de que no las mencionas):

1) Un usuario puede realizar una o más actividades.
2) Cada actividad con horario se desarrolla en una comisión.
3) Cada comisión tiene un único horario y día.
4) Una misma actividad puede desarrollarse en varios horarios diferentes en diferentes días.
5) Una misma actividad en un mismo horario deberá ser coordinada por una sola persona.
6) Una misma actividad en un mismo horario puede contar con asistentes de coordinación.
7) Un mismo miembro del staff puede ser titular de una o más actividades y asistente de otras, siempre que no se superpongan horarios.

Ese sería un esquema básico de restricciones, que se debe definir antes de ponerse a identificar las entidades, las cuales se deben determinar antes de pensar en decir cuáles tablas se usarán.
¿Por qué antes?
Porque de establecer la relación de cardinalidades entre los diferentes elementos que relacionan los objetos, surge cuál es la relación de tablas a crear.

En este esquema mínimo encontramos:
- Usuarios.
- Actividad.
- Comision.
- Horario.
- Coordinador.
- Asistentes.

(Si esto parece exagerado en cantidad de tablas, te cuento que ese ejemplo se usa en las clases de las universidades en las asignaturas de Bases de Datos, y en realidad la solución implica un mínimo de 12 tablas y hasta 17, según los parámetros que se den)

La idea de esta "atomización" de las relaciones es:
- Proteger la integridad de datos.
- Mantener la consistencia de datos.
- Evitar la redundancia innecesaria.
- Reducir los requerimientos de almacenamiento.

En este conjunto, las relaciones que se consultan serán, por ejemplo Usuarios, Actividades, Comisiones y Horarios, para saber qué horarios y cuántos tiene cada usuario, por ejemplo.
Tales consultas son menos complejas de lo que parecen, precisamente porque esta "atomización", luego de aplicar la normalización, permite crear sentencias de gran simplicidad que devuelven una cantidad eficiente de registros.

En tu caso, y según el ejemplo que planteo, las tablas podrían ser:
USUARIO(username, nombre, apellido, ...)
ACTIVIDAD(actividad_id, denominacion, otros...)
USUARIO_ACTIVIDAD(username, comision_id, fechaInscrip)
COMISION(comision_id, horario_id, Actividad_id, otros...)
HORARIO(horario_id, inicio, fin, fecha, comision_id, otros...)
COORDINADOR(staff_id, comision_id, otros...)
ASISTENTE(staff_id, comision_id, otros...)
STAFF(staff_id, Nombre, apellido, otros...)

Detalles:
- La aparición de una tabla adicional como es usuario_actividad, se debe a que toda relación N:N entre dos entidades crea una tabla.
- No hay relación directa entre un usuario y una actividad, porque un usuario se inscribe en la comisión, no en la actividad, ya que es la comisión la que debe ser de una actividad (cuestiones conceptuales).
- Hay dos tablas con el mismo contenido porque son entidades diferentes: Coordinador y Asistente. Coordinador es mandatoria (debe haber uno),mientras la relación con la otra en opcional (puede haber uno); si unificas ambos en la misma tabla tendrás el problema que te hará aparecer otra (una de categorías con la Comision) y agregará campos en la de comisión para vincularla.

Dejo aquí para que me digas si la cosa se va entendiendo.
Cualquier duda te sugiero que le des una mirada (si nunca viste el tema) al PDF cuyo link te puse.

gnzsoloyo · #5 (**permalink**) 11/10/2009, 08:54

Un tip final:
Nunca te olvides que mientras mejor esté creado el modelo de datos (el conjunto de tablas), menos "parches" le tendrás que hacer, parches que pueden, en el peor de los casos, obligarte a hacer migraciones de datos entre tablas de enorme complejidad... que te hubieses evitado haciendo un modelo eficiente de entrada.
Ten en cuenta que cuando la base esté en funcionamiento, modificarla es una calamidad.

Lo digo por experiencia personal...

3l3azar · #6 (**permalink**) 12/10/2009, 10:57

gnzsoloyo, ante todo quiero agradecerte el tiempo que has dedicado a responderme. No recuerdo ni la primera tutoría en la universidad en la que un profesor pusiera tanto empeño en enseñarme algo. El PDF me resultó bastante esclarecedor, y tus explicaciones mucho más. Muchas gracias, de verdad.

Creo que debo explicar mejor el objeto de mi base de datos. Se trata de proporcionar a unos usuarios una lista de actividades que se ajusten a lo que están buscando. Para ello, los usuarios establecen un horario de disponibilidad y la base de datos les ofrece las actividades que se desarrollan dentro de ese horario. No se si se entiende.

Dicho esto, gnzsoloyo, he intentado aplicar los conceptos a los que haces referencia. De este modo, creo que las restricciones de mi modelo serían:

- Un usuario puede realizar una o más actividades.
- Un usuario puede asistir a una o varias comisiones.
- Un usuario puede tener muchos horarios para cada día.
- Cada actividad se desarrolla en una o varias comisiones.
- Una misma actividad puede desarrollarse en varios horarios diferentes en diferentes días.
- Cada comisión tiene un único horario y día.

Por lo que los elementos serían los siguientes:

- Usuarios.
- Actividad.
- Comisión.
- Horario.

Como tu expusiste, las relaciones a consultar serían: Usuarios, Actividades, Comisiones y Horarios. Y ahora las tablas:

- USUARIO (user_id, username...)
- ACTIVIDAD (actividad_id, denominación...)
- COMISIÓN (comision_id, horario_id, actividad_id...)
- HORARIO (horario_id, inicio, fin, día de la semana...)
- USUARIO_ACTIVIDAD (user_id, comision_id...)

No se que tal lo he hecho. Tengo algunas dudas acerca de esto. En primer lugar:

Cita:

Iniciado por gnzsoloyo

- No hay relación directa entre un usuario y una actividad, porque un usuario se inscribe en la comisión, no en la actividad, ya que es la comisión la que debe ser de una actividad (cuestiones conceptuales).

No entiendo esto. Si una actividad puede tener una o mas comisiones, y una comision solo puede tener un horario, por qué no decir directamente que una actividad puede tener uno o mas horarios, y eliminar el paso intermedio? Crearía la tabla ACTIVIDAD_HORARIO para relacionar los dos elementos igual que creo la de USUARIO_ACTIVIDAD, pero sin necesidad de añadir un elemento más. Siempre considerando que, como tu dices, un usuario se "inscribe" en un horario, no en una actividad. Es correcto?

gnzsoloyo · #7 (**permalink**) 13/10/2009, 05:20

Cita:

No se que tal lo he hecho. Tengo algunas dudas acerca de esto. En primer lugar:
Cita:
Iniciado por gnzsoloyo Ver Mensaje

Cita:

- No hay relación directa entre un usuario y una actividad, porque un usuario se inscribe en la comisión, no en la actividad, ya que es la comisión la que debe ser de una actividad (cuestiones conceptuales).

No entiendo esto. Si una actividad puede tener una o mas comisiones, y una comision solo puede tener un horario, por qué no decir directamente que una actividad puede tener uno o mas horarios, y eliminar el paso intermedio? Crearía la tabla ACTIVIDAD_HORARIO para relacionar los dos elementos igual que creo la de USUARIO_ACTIVIDAD, pero sin necesidad de añadir un elemento más. Siempre considerando que, como tu dices, un usuario se "inscribe" en un horario, no en una actividad. Es correcto?

Son cuestiones un poco conceptuales, que se vuelven visibles a medida que te adentras en el tema del diseño de bases de datos.
Tienen que ver con los verbos que usas para determinar las relaciones; de allí surge más claridad de por qué los DBA Developer construyen las tablas como lo hacen. Digamos así:
Cuando dices "es de" o "pertenece a", estás estableciendo una relación directa entre dos entidades, muy probablemente 1:1 o 1:N; pero cuando dices "tiene" o "puede tener", es una relación N:N (el "debe tener" indica solamente que la relación tiene en uno de sus extremos una cardinalidad 1:N, es decir, debe haber por lo menos un caso que relacione una tabla con la otra; para el caso, para dar de alta una comisión debe haber por lo menos un usuario).
Eso te puede ayudar a determinar las cardinalidades de las relaciones.

Ahora bien, el descubrir algunas relaciones que son un poco abstractas requiere un poco más de pensamiento orientado a objetos. Lo que debes descubrir es donde se producen los puntos de unión, y si esos puntos de unión conforman entidades con identidad propia o son parte de otra. El hecho de que sean parte de otra es relativamente fácil de ver: ¿Puede existir esa entidad sin los atributos que están en otra? Si la respuesta es sí, es otra entidad. Si la respuesta es no, estás viendo atributos de una entidad (caso excepcional: jerarquías, ves una subentidad de otra superior).

Veamos un ejemplo como el tuyo:
Un usuario realiza actividades, no las tiene. No las tiene porque no son parte de su identidad de usuario, ni tampoco son obligatorias en el sistema. Si las realiza, debe haber un punto donde se determine qué actividades realiza. Esto se puede ver en que un usuario no tiene una actividad, y una actividad no es de un usuario.

Una comisión, por otra parte, no pertenece a una actividad, sino en todo caso a la facultad, la escuela, el instituto, o lo que sea que forme una parte de la organización, ya que es la organización lo que le da existencia. Pero parte de su identidad está dado por la actividad. Es una comisión de una actividad. Por lo tanto, está relacionada con la actividad.

En este modelo, entonces el usuario puede realizar una actividad, pero la actividad se realiza por medio de comisiones. Luego, una comisión tiene usuarios que se inscriben en ella para realizar la actividad.
Como se puede ver, la relación entre los usuarios y la actividad está en realidad gestionada por una tabla que es COMISION_USUARIO.

¿Dónde queda entonces Horario...?

Bueno, en este modelo básicamente hay dos formas de gestionarlo: o hacer una tabla COMISION_HORARIO que contenga la relación, o, mejor aún, hacer una tabla HORARIO que gestione las comisiones creadas con período de vigencia (inicio Y fin), y manejar el reparto de la grilla de horarios dinámicamente en la aplicación... cosa que es la forma en que muchas universidades manejan, porque en definitiva la superposición horaria en sistemas de cursos que no son prefijados, se manejan por usuario (alumno) y no por cursada...

¿Se va comprendiendo la lógica?

3l3azar · #8 (**permalink**) 14/10/2009, 05:15

Creo que empiezo a comprender... Un horario (por ejemplo: lunes de 16:00 a 17:00) puede contener varias comisiones (1:N), y cada comisión pertenece a una actividad, la cual puede tener varias comisiones (1:N). De este modo, horario y actividad (N:N) quedan relacionados. Eliminando la entidad comision, un horario puede tener varias actividades, y estas a su vez varios horarios (N:N), por lo que las tablas no se podrían relacionar directamente.

Entonces lo que necesito es:

- USUARIO (user_id, username...) -> Donde guardo los datos de los usuarios.
- ACTIVIDAD (actividad_id, denominación...) -> Donde guardo información de la actividad, pero no su horario.
- HORARIO (horario_id, inicio, fin, día de la semana...) -> Donde se almacenan los distintos horarios posibles.
- COMISIÓN (comision_id, horario_id, actividad_id...) -> La tabla que relaciona cada actividad con unos horarios, habiendo una comisión por cada horario de cada actividad.

Ahora tengo que relacionar USUARIO con HORARIO (N:N), para saber de que horarios dispone el usuario, por lo que necesito otra tabla de vinculación; que podría ser:

- USUARIO_HORARIO (user_id, horario_id...)

Además, si en un futuro necesitara que la base de datos me proporcionara información de la participación en las actividad, necesitaría la tabla:

- USUARIO_ACTIVIDAD (user_id, comision_id...)

para relacionar unívocamente a cada actividad con cada usuario.

¿Es correcto?

Ahora se me plantea otra duda, debido a mi desconocimiento del procesamiento de bases de datos. Pongamos que cada usuario tiene del orden de 20 horarios disponibles, y que la base de datos registra tan solo 100 usuarios. La tabla USUARIO_HORARIO ya contaría con 2.000 registros. Igual este ejemplo no es el más evidente, porque todavia son numeros pequeños, pero el caso es que una tabla de estas caracteristicas puede llegar a ser muy grande. Por otra parte, las operaciones necesarias para hacer una consulta si la información está almacenada de este modo son muchas menos. La cuestión es: debo entender que la eficiencia de una base de datos depende mucho más de un correcto modelo de relación entre los datos que no de un tamaño reducido, y que dando esto por sentado el modelo de entidad relación es la mejor forma de hacerlo, no es así?

gnzsoloyo · #9 (**permalink**) 14/10/2009, 05:57

USUARIO_HORARIO es un poco redundante, porque como el horario le pertenece a la Comisión, la relación de usuario con el horario está dada en forma indirecta.
En este caso, si quieres acceder a una información consolidada de este tipo, yo pensaría en una vista (VIEW), que me devolviese esa información en forma de una tabla. En definitiva, ese es uno de los usos de las VIEWs. Lo mismo haría con la otra (USUARIO_ACTIVIDAD), ya que en definitiva es información cuyos datos primarios están ya almacenados en otra parte, y se consultan de ese modo según lo que se requiera.
Como verás, hay ciertas cosas que no se resuelven creando tablas...

Fuera de eso, una vez definidas las tablas y los datos que se almacenan, para evitar el problema que causa tu duda, lo que se hace es diseñar el conjunto de consultas que esa base va a responder en forma general (las consultas más detalladas van apareciendo conforme se diseña el modelo de la aplicación). Cuando se tienen las consultas básicas puedes trabajar optimizándolas, entre otras cosas, creando índices que te evitarán los cuellos de botella cuando debas procesar muchos registros.
De todos modos te cuento que para que MySQL se empiece a sentir "pesado" respondiendo consultas bien diseñadas, es que estarás procesando mo unos miles de registros, sino algunos millones.
En cualquier caso, cuando llegues a esa etapa hay algmunos consejos básicos que podemos ver.

Cita:

La cuestión es: debo entender que la eficiencia de una base de datos depende mucho más de un correcto modelo de relación entre los datos que no de un tamaño reducido, y que dando esto por sentado el modelo de entidad relación es la mejor forma de hacerlo, no es así?

Exactamente.
Mientras mejor diseñado esté el modelo de datos no solamente tendrás mayor flexibilidad con las aplicaciones que lo puedan usar, sino mayor será la eficiencia que tenga.
El modelo E-R no es el único modelo que existe en bases de datos, pero se ha mostrado como el más eficiente y eficaz de ellos, por lo cual ha sido implementado por todas las empresas que diseñan sistemas de gestión de bases de datos (DBMS), tales como IBM, Oracle, Microsoft y un larguísimo etcétera.

3l3azar · #10 (**permalink**) 15/10/2009, 03:50

No creo que USUARIO_HORARIO sea redundante. Voy a poner un ejemplo: tengo un usuario 'user01', que tiene libre los lunes desde las 18:00 hasta las 20:00. Supongamos que los horarios 18:00-19:00 y 19:00-20:00 del lunes tienen el horario_id = 05 y 06 respectivamente. Ahora tengo la actividad 'act01', que se desarrolla en dos comisiones: lunes de 19:00 a 20:00 y jueves de 19:00 a 20:00 (horario_id = 06 y 25 respectivamente); y la actividad 'act02', los martes de 18:00 a 19:00 (horario_id = 10). Yo lo que quiero saber es que actividad o actividades puede realizar user01:

Código Tablas:

Ver originalUSUARIO_HORARIO         |                HORARIO                |                  COMISION                    |         ACTIVIDAD
user_id    horario_id   |  horario_id    dia    (ini  -  fin)   |  horario_id    comision_id    actividad_id   |   actividad_id      act
 
    01        05               05        lun     (18  -  19)
    01        06               06        lun     (19  -  20)           06            01             01                   01         act01
                               10        mar     (18  -  19)           10            02             02                   02         act02
                               25        jue     (19  -  20)           25            03             01                   01         act01

De este modo puedo relacionar a user01 con act01, que es la unica actividad de mi base de datos que user01 puede realizar. Crees que esta estructuración de los datos es suficientemente eficiente?

En cuanto a la otra tabla, USUARIO_ACTIVIDAD, tambien la necesito, puesto que, siguiendo con el ejemplo anterior, que user01 pueda asistir a act01 no implica necesariamente que lo vaya a hacer, por lo que en algún sitio tengo que almacenar las comisiones a las que asiste cada usuario.

Cita:

Iniciado por gnzsoloyo

El modelo E-R no es el único modelo que existe en bases de datos, pero se ha mostrado como el más eficiente y eficaz de ellos, por lo cual ha sido implementado por todas las empresas que diseñan sistemas de gestión de bases de datos (DBMS), tales como IBM, Oracle, Microsoft y un larguísimo etcétera.

Entiendo. Muchas gracias por la paciencia que estas teniendo conmigo gnzsoloyo

gnzsoloyo · #11 (**permalink**) 15/10/2009, 06:46

En realidad son redundantes, porque la relación con ambas informaciones surge de la tabla COMISION_USUARIO que te mencioné unos posts antes.
Esta tabla debería depender otra denominada COMISION_ASISTENCIA, que permita guardar la asistencia de los usuarios a esa comisión, así tendrías:
COMISION_USUARIO(comision_id, usuario_id, fecha_inscrip, otros datos)
COMISION_ASISTENCIA(comision_id, usuario_id, fecha).
De esta forma, en lugar de dos tablas adicionales tienes sólo una, y para saber quienes asisten a una actividad solo deberías hacer algo así:

Código sql:

Ver originalSELECT U.usuario_id, C.comision_id, actividad_id
FROM usuario U INNER JOIN comision_usuario USING(usuario_id) 
     INNER JOIN comision C USING(comision_id)
     INNER JOIN actividad A USING(actividad_id)
ORDER BY actividad_id, comision_id, usuario_id;

(el ORDER BY es opcional en este caso, depende de cómo lo quieras ordenar)
Si sólo quieres las actiidades en que está inscripto:

Código sql:

Ver originalSELECT DISTINCT U.usuario_id, U.Apellido, U.Nombre, A.denominacion
FROM usuario U INNER JOIN comision_usuario USING(usuario_id) 
     INNER JOIN comision C USING(comision_id)
     INNER JOIN actividad A USING(actividad_id)
ORDER BY usuario_id, actividad_id;

Si lo que quieres es ver los horarios de un usuario:

Código sql:

Ver originalSELECT DISTINCT U.usuario_id, U.Apellido, U.Nombre, A.denominacion, H.dia_sermana, H.Inicio, H.Fin
FROM usuario U INNER JOIN comision_usuario USING(usuario_id) 
     INNER JOIN comision C USING(comision_id)
     INNER JOIN actividad A USING(actividad_id)
     INNER JOIN horario H USING(horario_id)
ORDER BY u.usuario_id, A.actividad_id, h.dia_semana, h.inicio;

Precisamente por esto decía que una tabla para conservar esta información es redundante: porque tendrás dos tablas, al menos, donde se repite una relación de datos (sea esta directa o indirecta).

3l3azar · #12 (**permalink**) 16/10/2009, 05:02

gnzsoloyo, creo que no nos estamos entendiendo. No es eso lo que yo quiero hacer. El objetivo de la consulta es saber las actividades que pueden realizar los usuarios en función de su horario, no las actividades que los usuarios realizan. Yo quiero relacionar las actividades que se realizan en la franja horaria que un usuario i tiene disponible con el ususario i. En el ejemplo que puse en el post anterior, una cosa así:

Código:

SELECT U.user_id, C.horario_id, A.actividad_id
FROM usuario U INNER JOIN usuario_horario USING (user_id)
     INNER JOIN comision C USING (horario_id)
     INNER JOIN actividad A USING (actividad_id)
ORDER BY usuario_id, actividad_id

Lo que me devolvería un único registro:

user01 -> user_id = 1 -> horario_id = 6 -> comision_id = 1 -> actividad_id = 1 -> act01

que relaciona al usuario user01 con la actividad act01, que de todas las actividades de la base de datos, es la unica que tiene alguna comisión que se desarrolla en una franja horaria que el usuario user01 tiene disponible.

En cuanto a la tabla COMISION_USUARIO, esencialmente estamos de acuerdo, ya que yo la llamé USUARIO_ACTIVIDAD pero a pesar de tener distinto nombre en realidad son la misma tabla, ya que los dos la definimos así: (comision_id, usuario_id...). Lo que parece evidente es que tu nomenclatura resulta más acertada.