Creación de relaciones entre tablas con MySQL y PHPMyAdmin

por | 9 mayo, 2017

En nuestro proyecto ‘Sistema de riego‘ necesitaremos interrelacionar las tablas o entidades existentes para dar consistencia a los datos almacenados y facilitar tanto su gestión como el acceso posterior a los mismos, poco sentido tendría toda la información en una única tabla a modo cajón desastre o almacenar la información en múltiples tablas a lo loco y sin sentido.

Usaremos como ejemplo las tablas ‘T_SENSOR‘ y ‘T_SENSOR_AUDIT‘ existentes en nuestro modelo de base de datos.

  • T_SENSOR: Contendrá información básica y características de cada sensor, es decir, identificador único de sensor, tipo y estado. Puede que no le veamos sentido ahora pero tendrá su utilidad, a futuro por ejemplo usaremos esta tabla para comprobar si un sensor está activado y que tipo de información esperamos de él.
  • T_SENSOR_AUDIT: Esta tabla almacenará la información que nos proporcionará cada sensor (valor en grados centígrados, valor en nivel de humedad,…) en un momento dado, es decir, el valor registrado por un sensor concreto en una fecha, hora, minuto y segundo concreto.

Imaginemos la siguiente situación:

En la tabla ‘T_SENSOR_AUDIT‘ vemos como se almacena el histórico de valores en temperatura, humedad, intensidad luminosa,… de cada sensor siempre y cuando en ese instante estuviera activo, así mismo, también almacenamos el estado de ese sensor en un momento dado para saber si estaba activo o no, es decir, ‘T_SENSOR_AUDIT‘ guarda el estado/valor de un sensor en una fecha, minuto y segundo concretos, sin embargo, ‘T_SENSOR‘ nos muestra el estado activo/inactivo ahora mismo.

Estas informaciones son útiles dependiendo de que queramos hacer, ‘T_SENSOR‘ nos servirá para controlar un sensor concreto en el presente y ‘T_SENSOR_AUDIT‘ nos servirá más bien para hacer estudios, análisis y cruces de datos, gráficas bonitas,… o como comúnmente llaman ahora a esto las grandes empresas chachiguays ‘Reporting’.

Para que los datos almacenados tengan consistencia debemos establecer relaciones entre ambas tablas que eviten introducir datos erróneos, es decir, poco sentido tendría intentar almacenar información de un sensor identificado con el número ‘5’ sobre ‘T_SENSOR_AUDIT‘ si no existe realmente dado de alta en la tabla principal ‘T_SENSOR‘. Estas referencias entre tablas se llevan a cabo a través de lo que comúnmente se denominan ‘Llaves foráneas‘ o ‘Foreign keys‘ que mola más y queda más pro.

En nuestro ejemplo el campo ‘SENSOR_ID‘ de la tabla ‘T_SENSOR_AUDIT‘ sería una llave foránea, foreign key, referencia, link,… o como queramos llamarlo al campo ‘ROW_ID‘ de la tabla ‘T_SENSOR‘.

Una vez comentado lo anterior, nos gustaría hacer un par de anotaciones para facilitar la comprensión, fijar ciertas siglas o acrónimos y seamos sinceros para escribir menos palabrejos raros 🙂

  • Se denomina ‘Primary Key‘ al campo que identifica de forma única e inequívoca a un registro en una tabla de base de datos. Haremos referencia a este concepto con las siglas ‘PK‘.
  • Se denomina ‘Foreign Key‘ al campo de la tabla hija que hará referencia al campo que ejerce como ‘PK‘ de la tabla padre para establecer una relación entre ambas tablas. Haremos referencia a este concepto con las siglas ‘FK‘.
  • El método más común para hacer referencia a un campo concreto de una tabla utilizando el nombre de la tabla y el nombre del campo separados por un punto de este modo ‘NombreTabla.NombreCampo‘, en nuestro caso un par de ejemplos claros serían ‘T_SENSOR.ROW_ID‘, ‘T_SENSOR_AUDIT.ESTADO‘ o  ‘T_SENSOR_AUDIT.FECHA‘.

Dicho esto podemos continuar dando un ejemplo sobre PHPMyAdmin, para ello usaremos la base de datos ‘BD_AUTORIEGO‘ creada en el artículo anterior del proyecto.

Estableceremos el foco en PHPMyAdmin sobre la tabla ‘T_SENSOR_AUDIT‘ y la pestaña ‘Estructura‘ tal como mostramos en la siguiente imagen.

En esta pantalla pulsaremos sobre el icono ‘Vista de relaciones‘ para poder establecer relaciones, enlaces o asociaciones entre esta y otras tablas de la base de datos.

Al pulsar dicho botón nos dirigirá a la siguiente pantalla

En esta pantalla cumplimentaremos los datos tal cual se muestra en la siguiente imagen y pulsaremos ‘Guardar‘.

Esta configuración significa lo siguiente:

  • El campo ‘T_AUDIT_SENSOR.SENSOR_ID‘ está directamente asociado al campo ‘T_SENSOR.ROW_ID‘, así pues, no podremos insertar un valor en el primero que no exista o se haya dado de alta previamente en la tabla ‘T_SENSOR‘.
  • En caso de ser borrado un registro en la tabla  ‘T_SENSOR‘ todos los registros de la tabla ‘T_AUDIT_SENSOR‘ relacionados con dicho ‘T_SENSOR.ROW_ID‘ serán borrados en cascada.
  • En caso de ser actualizado un registro en la tabla  ‘T_SENSOR‘ todos los registros de la tabla ‘T_AUDIT_SENSOR‘ relacionados con dicho ‘T_SENSOR.ROW_ID‘ serán actualizados en cascada.

Para hacer una prueba sencilla y demostrar que esto es verdad navegaremos a la pantalla ‘SQL‘ a través de la botonera superior de PHPMyAdmin.

Esta ventana nos permite construir comandos de consulta, actualización, inserción,… en lenguaje SQL. No entraremos en detalle de SQL pues es un lenguaje que explicaremos con más detalle en otros manuales, en esta ocasión únicamente facilitaremos los comandos básicos para nuestra prueba.

1. Creación de registros en tabla ‘T_SENSOR’:

Introduciremos el siguiente comando en SQL:

INSERT INTO `t_sensor`(`ROW_ID`, `TIPO`, `ESTADO`) VALUES (1,’Temperatura’,’Activo’);

INSERT INTO `t_sensor`(`ROW_ID`, `TIPO`, `ESTADO`) VALUES (2,’Humedad’,’Activo’);

2. Consulta de registros en tabla ‘T_SENSOR_AUDIT’:

Para llevar a cabo consultas generales utilizaremos el siguiente comando:

SELECT * FROM `t_sensor` WHERE 1

Tras la inserción de registros en el paso 1 el resultado debería ser el siguiente:

3. Creación de registros en tabla ‘T_SENSOR_AUDIT’:

Esta segunda creación de registros será dependiente de la primera, es decir, no podremos insertar un identificador de sensor en la columna ‘T_AUDIT_SENSOR.SENSOR_ID‘ si no existe previamente en ‘T_SENSOR.ROW_ID‘.

Para demostrar esto intentaremos crear dos registros con los siguientes comandos SQL:

INSERT INTO `t_sensor_audit`(`ROW_ID`, `SENSOR_ID`, `ESTADO`, `VALOR`, `FECHA`, `TIPO`) VALUES (1,1,’Activo’,24,CURRENT_TIMESTAMP,’Lectura’)

INSERT INTO `t_sensor_audit`(`ROW_ID`, `SENSOR_ID`, `ESTADO`, `VALOR`, `FECHA`, `TIPO`) VALUES (2,4,’Activo’,24,CURRENT_TIMESTAMP,’Lectura’)

¡QUE NO PANDA EL CUNICO!, si os aparece el siguiente error rojo chillón es totalmente normal y precisamente lo que buscábamos.

Este error lo que nos viene a decir es que no podemos crear un registro para auditar en la tabla T_AUDIT_SENSOR’  por que no hemos dado de alta el sensor que deseamos auditar en la tabla ‘T_SENSOR

¿Empieza a coger forma la idea?, esperamos que toda esta traca os ayude tanto a aprender teoría de base de datos como a comprender poco a poco como vamos construyendo los cimientos de nuestro sistema de riego.

Nos vemos en el siguiente manual!!

Comparte esta página! Email this to someone
email
Share on Facebook
Facebook
0Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin

Deja un comentario

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.