Curso MariaDB 6: Relación sql MANY to MANY y finalizamos el proyecto

Para finalizar el proyecto vamos con la tabla de clientes y sus direcciones para ello nos queda la creación de 3 tablas cb_customer, cb_addresses y cb_address que nos permitirán afianzar el conocimiento de SQL, y aprender en este caso como construir relaciones SQL many to many, explicaremos los diferentes tipos de relaciones que pueden existir entre bases de datos y que ya hemos visto como one to one en las relaciones de las tablas de idiomas, monedas, países y métodos de pago con las tablas que los utilizaban, además también se pueden establecer relaciones one to many como veremos en la relación entre empresas y clientes, ya que una empresa puede estar asociada a muchos clientes.

1. Explicamos teórícamente las relaciones entre tablas
   • ONE TO ONE
   • ONE TO MANY
   • MANY TO MANY
2. Creación de cb_addresses
3. Creación de cb_address
4. Creación de cb_customerdb
5. Datos y ejemplo de funcionamiento
6. Documentación utilizada

1. Explicamos teóricamente las relaciones

A lo largo del curso, hemos ido aprendiendo sin darnos cuenta las relaciones entre tablas con la creación de claves foráneas. Las relaciones que hemos establecido hasta ahora son todas del tipo one to many , es decir, uno a uno, por ejemplo, en la tabla de países podemos tener un idioma y una moneda, para ello se establece una relación one to many con las tablas correspondientes.

Cómo veremos a continuación hoy veremos otros dos tipo de relaciones que salen tamblén de la relación entre tablas mediante claves foráneas que son one to one (es decir, uno a uno) y many to many (muchos a muchos).

ONE TO ONE

En la base de datos Learning Project no tenemos ninguna relación de este tipo así que para poner un ejemplo, vamos a crear una explicando la relación one to one.

Una relación one to one consiste en la relación entre una tabla A y una tabla B, cuando el registro de la tabla B solo puede pertener a un registro de la tabla A, por ejemplo supongamos que la tabla A es la tabla de empresas (cb_enterprise) y la tabla B es una tabla con datos adicionales fiscales de la empresa (cb_taxdata), que significa esto:

Una empresa tendra un único registro en la tabla de datos fiscales.

Los datos fiscales solo pueden pertener a una empresa, son únicos para cada una.

Relación ONE TO ONE entre una tabla empresa y una tabla datos fiscales

Las tablas se relacionan mediante una clave foránea, pero el registro creado para los datos fiscales solo puede pertener a una empresa. La diferencia la podéis ver claramente con las relaciones que tiene una empresa y el país asociado: una empresa puede tener un único país, pero un país puede estar asignado a múltiples empresas

ONE TO MANY

Las relaciones de claves foráneas que hemos establecido en este proyecto siguen está relación one to many, que consiste en la asignación de un registro de una tabla B a un registro de una tabla principal A, ese registro al contrario que en el caso anterior puede estar asignado a múltiples registros en la tabla A.

Esto lo vemos con claridad si lo explicamos en la tabla cb_country que tiene una relaciones one to many con cb_language y cb_currency, a un país se le asigna una moneda, por ejemplo: España y la moneda Euro, esa moneda no es exclusiva para España sino que otros países también pueden tener asignada esa moneda, por ejemplo: Francia y moneda asignada Euro; en esto consiste la relación one to many. La relación one to many con la tabla de idiomas cb_language se explica de la misma forma.

Un país tiene asignada una moneda: España y moneda Euro.

La moneda Euro puede estar asignada a múltiples países: Francia, Alemania, …

Relación ONE TO MANY entre la tabla de paises cb_country y de monedas cb_currency

En nuestro proyecto esta relación la tenemos entre empresas y clientes, es decir, una empresa puede tener múltiples clientes.

MANY TO MANY

Este tipo de relaciones son más complicadas de entender cuando estás empezando que las anteriores, es por eso que hemos dejado para la última publicación del curso la explicación de estas relaciones, de la cual veremos dos formas de realizarla.

La relación many to many la utilizamos cuando para una tabla A necesitamos tener asociados múltiples registros de la tabla B, el caso que vamos a utilizar en este ejemplo es que queremos para un cliente tener múltiples direcciones, si tenemos una tabla donde se registran las direcciones, necesitamos una tabla intermedia para registrar todas los registros de la tabla que pertecen a cada cliente.

Cuando para una tabla A necesitamos tener asociados múltiples registros de la tabla B, por ejemplo para un cliente con múltiples direcciones

Relación MANY TO MANY

Lo podemos hacer de dos formas:

1. Tabla intermedia donde registramos: el id del cliente y el id de las direcciones
2. Tabla intermedia con un identificador propio: ese identificador se crea como registro tanto en la tabla de clientes como en la de direcciones permitiendo así la relación de las tabla de clientes con todas su direcciones.

La primera forma es la más ortodoxa conforme a las reglas SQL, la segunda forma es más cómoda de definer desde el lado de las aplicaciones, en nuestro caso veremos como crear la opcion 2 para el proyecto Learning Project

2. Creación de cb_addresses

Está es la tabla intermedia que nos va a permitir establecer la relación entre los clientes (cb_customer) y sus direcciones (cb_address) en este caso utilizamos un campo único que generamos en la tabla cd_addresses esto nos va a permitir utilizar esta tabla no solo para cb_customer sino también para otras tablas, el campo id (idaddesses) se creará tanto en cb_customer como en cb_address permitiendo la relación directa.

2.1 Creación de la tabla

La tabla cb_addresses es la tabla donde se asocian las direcciones que tiene un cliente , cada cliente tendrá un número ilimitado de direcciones definidas en cb_address, que se asociarán mediante el id generado en esta tabla y que se relacionará directamente con el cliente.

Está es la definición de la tabla, que definimos como una agrupación de las direcciones asignadas a una entidad:

CREATE TABLE cb_addresses
(
  idaddresses INT NOT NULL AUTO_INCREMENT,
  addressesentity VARCHAR(100) NOT NULL,
  CONSTRAINT pk_cb_addresses PRIMARY KEY (idaddresses)
)
ENGINE=InnoDB
COMMENT='Agrupación de las direcciones asignadas a una entidad.';
GRANT ALL ON TABLE cb_addresses TO xulescode;

2.2 Otra posible solución

La otra forma de hacerlo sería crear una tabla donde relacionarmos directamente el id del cliente (idcustomer) con sus ids de direcciones (idaddress), quedando así:

CREATE TABLE cb_addresses
(
  idaddresses INT NOT NULL AUTO_INCREMENT,
  idcustomerdb INT NOT NULL, 
  idaddress INT NOT NULL,
  CONSTRAINT pk_cb_addresses PRIMARY KEY (idaddresses)
);

En esta solución solo faltaría indicar la currespondencia entre tablas con las claves foráneas (foreign keys), esto os lo dejo como ejercicio para quién quiera seguir investigando.

3. Creación de cb_address

La tabla  cb_address es la tabla de direcciones que vamos a definir para los clientes u otras entidades que lo necesiten, a través de la relación que establecemos mediante cb_addresses para cada entidad, como veremos a continuación y estableciendo una relación many to many, este es el código SQL de la tabla:

CREATE TABLE cb_address
(
  idaddress INT NOT NULL AUTO_INCREMENT,
  idaddresses INT,
  address VARCHAR(500),
  postalnumber VARCHAR(20),
  mainphone VARCHAR(100),
  movilephone VARCHAR(100),
  phone2 VARCHAR(100),
  phone3 VARCHAR(100),
  carrier VARCHAR(200),
  addresstype VARCHAR(100),
  locality VARCHAR(250),
  estate VARCHAR(250),
  idcountry INT,
  notes1 VARCHAR(500),
  CONSTRAINT pk_cb_address PRIMARY KEY (idaddress),
  CONSTRAINT fk_cb_address_idaddresses FOREIGN KEY (idaddresses)
      REFERENCES cb_addresses (idaddresses) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_cb_address_idcountry FOREIGN KEY (idcountry)
      REFERENCES cb_country (idcountry) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
ENGINE=InnoDB
COMMENT='Direcciones para: Clientes, Empresas,... ';
GRANT ALL ON TABLE cb_address TO xulescode;

Usamos idaddress como clave primaria: CONSTRAINT pk_cb_address PRIMARY KEY (idaddress), definimos los campos habituales para la dirección entre ellos los teléfonos y el país, para este usamos una clave foránea para relacionar el país con la dirección por medio de idcountry.

  CONSTRAINT fk_cb_address_idcountry FOREIGN KEY (idcountry)
      REFERENCES cb_country (idcountry) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION

Para almacenar todas las direcciones de una entidad las relacionamos por el campo idaddresses para el que podemos tener una o varias direcciones, este campo se genera en la tabla cb_addresses y para ello tenemos que relacioinarlo con una clave foránea:

  CONSTRAINT fk_cb_address_idaddresses FOREIGN KEY (idaddresses)
      REFERENCES cb_addresses (idaddresses) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,

De momento vamos a dejar las acciones sin hacer nada, tanto aquí como en cb_addresses y cb_address, para más adelante ver la conveniencia de realizar acciones de actualización y borrado en cadena.

Así quedan las tablas creadas y sus relaciones en el siguiente apartado añadiremos la tabla cb_customer:

4. Creación de cb_customer

La tabla cb_customer es la tabla donde se almacenarán los clientes de las diferentes empresas, se entiende cliente como aquel que compra a una empresa, veamos el código SQL para nuestra tabla de cliente.

CREATE TABLE cb_customer
(
  idcustomer INT NOT NULL AUTO_INCREMENT,
  identerprise INT,
  customer VARCHAR(15) NOT NULL,
  customername VARCHAR(150),
  customeralias VARCHAR(100),
  contact VARCHAR(250),
  customerstate VARCHAR(30),
  sale DECIMAL(10,3),
  identitynumber VARCHAR(100),
  customerpayer VARCHAR(20),
  idpaymentmethod INT,
  idcountry INT,
  idcurrency INT,
  idlanguage VARCHAR(6),
  idaddresses INT,
  CONSTRAINT pk_cb_customer PRIMARY KEY (idcustomer),
  CONSTRAINT fk_cb_customer_idaddresses FOREIGN KEY (idaddresses)
      REFERENCES cb_addresses (idaddresses) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_cb_customer_idcountry FOREIGN KEY (idcountry)
      REFERENCES cb_country (idcountry) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_cb_customer_idcurrency FOREIGN KEY (idcurrency)
      REFERENCES cb_currency (idcurrency) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_cb_customer_identerprise FOREIGN KEY (identerprise)
      REFERENCES cb_enterprise (identerprise) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_cb_customer_idlanguage FOREIGN KEY (idlanguage)
      REFERENCES cb_language (idlanguage) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_cb_customer_idpaymentmethod FOREIGN KEY (idpaymentmethod)
      REFERENCES cb_paymentmethod (idpaymentmethod) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT un_cb_customer_cb_enterprise UNIQUE (identerprise, customer)
)
ENGINE=InnoDB
COMMENT='Tabla donde se almacenarán los clientes de las diferentes empresas, se entiende cliente como aquel que compra a una empresa.';
GRANT ALL ON TABLE cb_customer TO xulescode;

Usamos idcustormer como clave primaria: CONSTRAINT pk_cb_customer PRIMARY KEY (idcustomer), como queremos separar los clientes por empresa necesitamos indicar a que empresa pertenece cada cliente, para ello usamos identerprise para relacionarlos con la empresa de la tabla cb_enterprise , como se puede ver en la tabla usamos la clave foránea: fk_cb_customer_identerprise para establecer la relación.

Además también definimos las relacionales para indicar el idioma (cb_language), la moneda (cb_currency) ,  el país (cb_country) y las formas de pago (cb_paymentmethod).

También, definimos para los clientes, lo hacemos a través de la tabla cb_addresses, tabla que utilizaremos para almacenar varias direcciones de cliente en la tabla cb_address. En la tabla de cb_customer simplemente establecemos la relación con cb_addresses con la columna idaddresses como hicimos en otros casos:

CONSTRAINT fk_cb_customer_idaddresses FOREIGN KEY (idaddresses)
	REFERENCES cb_addresses (idaddresses) MATCH SIMPLE
	ON UPDATE NO ACTION ON DELETE NO ACTION

Será a través de esta tabla como obtendremos las direcciones reales del cliente almacenadas en cb_address. En la siguiente imagen puedes ver como queda la relación entre las 3 tablas, así visualmente se entiende mejor:

5. Datos y ejemplo de funcionamiento

A continuación haremos varios INSERT encadenados para la creación de direcciones para varios clientes, esto nos permitirá hacer posteriormente unas consultas y ver el resultado final.

5.1 Un cliente con dos direcciones

Para crear un cliente con dos direcciones primero tenemos que crear un identificador en la tabla cb_addresses, y que utilizaremos posteriormente en las tablas cb_address y cb_customer.

El orden de creación del cliente (cb_customer) y de las direcciones (cb_address) es indiferente, es este ejemplo, primero creo las direcciones y después el cliente:

-- Cliente 1  con 2 direcciones
 INSERT INTO cb_addresses (addressesentity)
     VALUES('cb_customer');
 INSERT INTO cb_address (idaddresses, address, postalnumber, 
                         mainphone, movilephone, phone2, phone3, 
                         carrier, addresstype, locality, estate, idcountry, notes1)
     VALUES( 1, 'Genaro de la Fuente', 36216, 
             '578 95 84 39', '578 23 45 78', NULL, NULL, 
             'XEUR', 5, 'VIGO', 'GALICIA', 275, 'Solo en horario de mañana hasta las 15:00');
 INSERT INTO cb_address (idaddresses, address, postalnumber, 
                         mainphone, movilephone, phone2, phone3, 
                         carrier, addresstype, locality, estate, idcountry, notes1)
     VALUES( 1, 'Oscar Puente', 36216, 
             '578 95 84 45', '578 23 45 78', NULL, NULL, 
             'XEUR', 15, 'VIGO', 'GALICIA', 275, 'Dirección exclusiva para facturars. Solo en horario de mañana hasta las 15:00');
 INSERT INTO cb_customer(identerprise, customer, customername, customeralias, contact, customerstate, sale, identitynumber, customerpayer, 
                         idpaymentmethod, idcountry, idcurrency, idlanguage, idaddresses)
     VALUES( 100, 'JUANDI S.L.', 'Juan', 'Juan', 'Juan Díaz', 'ACTIVO', -2000, 'A99887766', 'JUANDI S.L.', 
             3, 275, 7, 'es_ES', 1);

La consulta para ver los datos del cliente con su dirección, se puede hacer relacionando direcgtamente la tabla cb_customer con cb_address:

SELECT cc.customer, cc.identerprise, ca.*
     FROM    cb_customer cc, cb_address ca 
     WHERE   cc.idaddresses  = ca.idaddresses AND 
             cc.idcustomer   = 1;

Este es el resultado:

5.2 Un cliente con tres direcciones

Para crear un cliente con dos direcciones primero tenemos que crear un identificador en la tabla cb_addresses, y que utilizaremos posteriormente en las tablas cb_address y cb_customer.

-- Cliente 1  con 2 direcciones
 INSERT INTO cb_addresses (addressesentity)
     VALUES('cb_customer');
 INSERT INTO cb_address (idaddresses, address, postalnumber, 
                         mainphone, movilephone, phone2, phone3, 
                         carrier, addresstype, locality, estate, idcountry, notes1)
     VALUES( 1, 'Genaro de la Fuente', 36216, 
             '578 95 84 39', '578 23 45 78', NULL, NULL, 
             'XEUR', 5, 'VIGO', 'GALICIA', 275, 'Solo en horario de mañana hasta las 15:00');
 INSERT INTO cb_address (idaddresses, address, postalnumber, 
                         mainphone, movilephone, phone2, phone3, 
                         carrier, addresstype, locality, estate, idcountry, notes1)
     VALUES( 1, 'Oscar Puente', 36216, 
             '578 95 84 45', '578 23 45 78', NULL, NULL, 
             'XEUR', 15, 'VIGO', 'GALICIA', 275, 'Dirección exclusiva para facturars. Solo en horario de mañana hasta las 15:00');
 INSERT INTO cb_customer(identerprise, customer, customername, customeralias, contact, customerstate, sale, identitynumber, customerpayer, 
                         idpaymentmethod, idcountry, idcurrency, idlanguage, idaddresses)
     VALUES( 100, 'JUANDI S.L.', 'Juan', 'Juan', 'Juan Díaz', 'ACTIVO', -2000, 'A99887766', 'JUANDI S.L.', 
             3, 275, 7, 'es_ES', 1);

La consulta para ver los datos del cliente con su dirección, se puede hacer relacionando direcgtamente la tabla cb_customer con cb_address:

SELECT cc.customer, cc.identerprise, ca.*
     FROM    cb_customer cc, cb_address ca 
     WHERE   cc.idaddresses  = ca.idaddresses AND 
             cc.idcustomer   = 1;

6. Eliminar tablas en SQL

Por si fuera necesario durante la programación el orden del borrado de tablas sería el siguiente:

DROP TABLE cb_customer;
DROP TABLE cb_enterprise;
DROP TABLE cb_paymentmethod;
DROP TABLE cb_address;
DROP TABLE cb_addresses;
DROP TABLE cb_country;
DROP TABLE cb_currency;
DROP TABLE cb_language;

Documentación utilizada

• Relaciones entre tablas de bases de datos por Ambar Quintana: buena explicación con ejemplos sobre los tipos de relaciones entre tablas.