Relacionar clientes con fecha de reserva

[davezorn00]

Hola a todos. Ante todo quiero decir que soy bastante novato en bases de datos y php. Acudo a vosotros para plantear un problema que me viene de hace días y ya no se como plantear. Os explico:

Estoy trabajando en una aplicación web de para pedir citas previas solicitando uno de estos servicios:
Asistencia Técnica
Consultas
Cursos

Uno de los apartados es un calendario en el que se selecciona la fecha que se quiere reservar. En el siguiente apartado hay un formulario dónde se ingresan los datos del cliente (nombre, apellidos, mail, telefono)

Tengo una base de datos con dos tablas:

calendario
idReserva
dia
hora

formulario
idReserva
nombre
apellidos
email
telefono
servicio

Todo funciona perfectamente, pero lo que quiero es relacionar los datos del cliente que efectúa la reserva, con el dia que ha seleccionado ya que eso es imprescindible.

Espero haberme explicado bien y acepto que probablemente haya un error en el planteamiento de la base de datos.

Os dejo la dirección por si quereis echar un vistazo:

Editado: Políticas de Uso de FDW

Gracias de antemano!

[quimfv]

Efectivamente tienes un error en el diseño de la bbdd.

reservas
idReserva
diahora (datetime)
idServicio
idCliente

clientes
idCliente
nombre
apellidos
email
telefono

servicios
idServicio
servicio
...

no se si con esto puedes hacerte una idea....

Esto te permitiria

Código MySQL:

SELECT c.idCliente,
             c.nombre,
             c.apellidos,
             c.email,
             c.telefono,
             r.idReserva,
             r.dia,
             r.hora,
             s.idServicio,
             s.servicio
FROM (clientes c INNER JOIN reservas r ON c.idClientes=r.idClientes)
                          INNER JOIN servicios s ON s.idServicio=r.idServicio
WHERE r.dia>=now();
 

obtener por ejemplo todos los datos de las reservas futuras....

[davezorn00]

Muchas gracias Quim. Probaré el diseño que propones!

[davezorn00]

Todo funciona perfectamente, excepto que no me deja insertar registros en la tabla `reservas`.

Este es el error que me tira:

Código MySQL:

INSERT INTO  `citapreviatest`.`reservas` (
 
`idReserva` ,
`dia` ,
`hora` ,
`idServicio` ,
`idCliente`
)
VALUES (
NULL ,  '2013-01-10', NULL ,  '',  ''
)
MySQL ha dicho: 
 
#1452 - Cannot add or update a child row: a foreign key constraint fails (`citapreviatest/reservas`, CONSTRAINT `reservas_ibfk_3` FOREIGN KEY (`idReserva`) REFERENCES `reservas` (`idReserva`) ON DELETE NO ACTION ON UPDATE NO ACTION)
 

Como soluciono esto??

Pongo la creación de las foreign keys por si sirve de algo y el error se encuentra ahí:

Código MySQL:

ALTER TABLE `reservas` ADD FOREIGN KEY ( `idServicio` ) REFERENCES `citapreviatest`.`servicios` (
`idServicio`
) ON DELETE CASCADE ON UPDATE CASCADE ;
 
ALTER TABLE `reservas` ADD FOREIGN KEY ( `idCliente` ) REFERENCES `citapreviatest`.`clientes` (
`idCliente`
) ON DELETE CASCADE ON UPDATE CASCADE ;
 

Gracias de antemano

[gnzsoloyo]

Es un tema de dependencias.
No puedes poner una reserva sin un cliente (¿quien reserva, en ese caso?), ni tampoco sin un servicio (¿qué se reservaría?). Los servicios y los clientes deben existir antes de ser ingresadas las reservas.
Uno de los objetivos de las FK es precisamente evitar que ingreses datos inconsistentes. Y eso es lo que estás intentando hacer.

[davezorn00]

En ese caso tengo un problema, puesto que en la aplicación web, el primer paso es elegir el servicio (Asistencia, Consultas o Cursos). Al elegir el servicio, cada uno de ellos deriva a una subestructura de carpetas dónde dependiendo de la selección, las urls serán: citaprevia/asistencia/... | citaprevia/consultas | citaprevia/cursos. El segundo paso es elegir un dia y una hora (tabla reservas). Una vez seleccionado, pasa a la siguiente pagina, dónde se encuentra el formulario a rellenar con los datos (tabla clientes). Finalmente pasa a una ultima pagina dónde se muestran todos los datos (fecha, datos) y dónde se muestra el nombre del servicio dependiendo de la url (citaprevia/asistencia/..., citaprevia/consultas/... o citaprevia/cursos/...) y se inserta en la tabla servicios de la BD.

He pensado que en lugar de establecer la relación mediante las claves primarias de servicios (idServicio) y clientes (idCliente), si la relación se hace añadiendo los campos idReserva en ambas tablas (servicios y clientes) y enlazándolo a la tabla reservas, si me dejaría ingresar primero la reserva y luego los clientes y servicios.

Quedando asi entonces la estructura:

reservas
idReserva
dia
hora


clientes
idCliente
nombre
apellidos
email
telefono
idReserva

servicios
idServicio
servicio
idReserva


Y estableciendo estas relaciones:

Código MySQL:

ALTER TABLE  `servicios` ADD FOREIGN KEY (  `idReserva` ) REFERENCES  `citapreviatest`.`reservas` (
`idReserva`
) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE  `clientes` ADD FOREIGN KEY (  `idReserva` ) REFERENCES  `citapreviatest`.`reservas` (
`idReserva`
) ON DELETE CASCADE ON UPDATE CASCADE ;
 

Aun así tengo el problema de que al ingresar una fecha y al pasar el siguiente paso de la aplicación (inserción de datos del cliente) como lo hago para que sepa que esa reserva (fecha) corresponde al cliente que acaba de ingresar los datos?
Seria viable insertar la misma id de idReserva de la tabla reservas en idReserva de la tabla clientes y en la tabla servicios a la misma vez con un insert múltiple? De ser posible cual seria la consulta?

Espero haberme explicado y muchísimas gracias por vuestra paciencia y tiempo.

[quimfv]

NOOOOOO....eso romperia todo el esquema relacional... tendrias tantos clientes como reservas y la idea es guardar una sola vez cada cliente...

Todo lo que cuentas me parece muy bien pero lo unico que no haces bien es intentar la inserción antes de tener todos los datos....

1. eliges servicio lo propagas a la pagina siguiente,
2. eliges fecha y hora (un SELECT sobre reservas te dira lo que esta libre. Quizas en el momento que se elige un dia deberias ofrecer la horas libres....) lo propagas a la pagina siguiente junto con el servicio.
3. Elgies o ingresas el cliente (aqui quizas tienes que hacer un INSERT INTO a clientes),

4. en este momento ya tenemos todos los datos con lo que podremos hacer el INSERT INTO a reservas.

Otro tema sera como controlas que una fecha y hora no se otorga a mas de un cliente, pero esa es una restricción que hasta el momento no habias declarado.... En este caso tambien hay un tema de control de concurrencia (dos clientes intentando reservar lo mismo al mismo tiempo... ese es un tema complicado y que no te podemos resolver sin ver todo tu proyecto)

[davezorn00]

Efectivamente Quim, los problemas de no otorgar una fecha y hora iguales a un cliente y el problema de que se estén efectuando dos reservas a la vez, ya los había tenido en cuenta, sólo que he preferido diseñar una estructura básica de la base de datos y luego en todo caso centrarme en control de errores. Tengo algunas ideas en mente de como hacer consultas a la tabla reservas y que me devuelva las horas insertadas como registros en otra tabla (ej.: horas) que no sean iguales al campo "hora" de la tabla reserva. El tema de las horas lo tengo aparcado por el momento, y me centro en que se pueda relacionar el dia seleccionado con el cliente que esta haciendo la reserva.

Por otra parte el hecho de que haya tantos clientes como reservas no es ningún problema, de hecho es lo que quiere mi cliente. Siempre se puede hacer una consulta basándose en un nombre de cliente para visualizar todas las reservas que ha realizado no?

Lo que me comentas de propagar las elecciones de página en página la verdad que no tengo ni idea de como plantearlo. Mis conocimientos en PHP también son bastante limitados.

Agradezco cualquier orientación que pudierais darme.
Mis disculpas por mi ignorancia y gracias de nuevo por vuestra paciencia.


Por cierto he probado con lo de devolver las horas disponibles en un dia de la siguiente manera:

Creo una tabla `fechahora` con todas las horas que pueden estar disponibles y las fechas que se han insertado en la tabla reservas (y que simultáneamente se insertan en este campo también):

Código MySQL:

CREATE TABLE `citapreviatest`.`fechahora` (
`horas` VARCHAR( 100 ) NOT NULL ,
`fechas` VARCHAR( 100 ) NOT NULL ,
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE = INNODB
 

Genero una consulta:

Código MySQL:

SELECT horas
FROM fechahora
WHERE (
SELECT fechas, horas
FROM fechahora
) 
NOT IN 
(
SELECT dia, hora
FROM reservas
)
 

Y el resultado es este:

Código MySQL:

MySQL ha dicho: 
 
#1242 - Subquery returns more than 1 row
 

Alguna idea? TT

[quimfv]

Por otra parte el hecho de que haya tantos clientes como reservas no es ningún problema, de hecho es lo que quiere mi cliente. Siempre se puede hacer una consulta basándose en un nombre de cliente para visualizar todas las reservas que ha realizado no?

Error de diseño, tu mismo.

Código MySQL:

CREATE TABLE `citapreviatest`.`fechahora` (
`horas` VARCHAR( 100 ) NOT NULL ,
`fechas` VARCHAR( 100 ) NOT NULL ,
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE = INNODB
 

Primer error horas y fechas con formato VARCHAR
Segundo error, invalidante, esta tabla es infinita... que vas a meter todas las horas posibles...

Código MySQL:

...
NOT IN
(
SELECT dia, hora
FROM reservas
)
 

IN o NOT IN espera una lista de valores, no una lista de pares de valores....

La comparación de las reservas con la horas no reservadas la tienes que hacer por programación, un bucle sobre las 24 horas de un dia te dira quales estan reservadas y cuales no y asi no tienes una tabla infinita con todas las posibles horas....

Lo que me comentas de propagar las elecciones de página en página la verdad que no tengo ni idea de como plantearlo. Mis conocimientos en PHP también son bastante limitados.

Por propagar quiero decir pasar por get o post los datos de una pagina a la otra... igual que los pasas para hacer el insert pero no haces insert hasta tener todos los datos.

....

[davezorn00]

Al final he creado una tabla `reservas` que contiene:

idReserva
dia
hora
nombre
apellidos
email
telefono
servicio

He ido pasando los datos por POST de página en página y finalmente se insertan todos los datos a la vez en la BD.

El problema que tengo ahora es que MYSQL me formatea las fechas en este formato: aaaa-mm-dd y yo busco algo así como dd/mm/aaaa. Es posible cambiar este formato?

Gracias de antemano!

[gnzsoloyo]

El problema que tengo ahora es que MYSQL me formatea las fechas en este formato: aaaa-mm-dd y yo busco algo así como dd/mm/aaaa. Es posible cambiar este formato?

Eso es un tema recurrente.
No. No se cambia el formato de MySQL, lo que se hace es cambiar el formato que se muestra en pantalla, y eso lo puedes hacer tanto en el programa (recomendable), como en la consulta final (opcional). Pero jamás se almacena una fecha como VARCHAR.

No hay que confundir nunca un dato almacenado, con un dato mostrado.
Son cosas levemente diferentes.