Durante el diseño de un correcto modelo relacional de una base de datos, no cabe la duda la implementación de un modelo genérico de tablas llamado “Atributos & Tipos” u otros lo llaman (EAV, Entity Attribute Value) en sus siglas en ingles, el cual aumenta la flexibilidad y robustez del mismo.

La intención de este tipo de “patrón” de diseño por llamarlo de alguna forma, es que permite tener un dominio detallado sobre todos los atributos que podamos asignarle a cualquier elemento que es almacenado, en pocas palabras, cada registro habla por si solo y no tendrá libre interpretación, las aplicaciones que hacen uso de estos valores serán dominados directamente por el manejador de base de datos, y al final todo este proceso conlleva a una simple solución de auto-documentación e integridad de los datos.

Implementar este patrón conlleva una serie de pasos, en primer lugar; tenemos las tablas que contendrán toda la clasificación de los atributos, en segundo lugar; cada tabla tendrá una relación sobre las tabla atributos, en tercer lugar; existirá una vista por cada tipo de atributo para ayudar a ser mas ordenados, por ultimo; se implementan una serie de trigger de control sobre las tablas relacionadas con los atributos, evitando cualquier confusión en su uso.

Manos a la obra!, observemos el siguiente modelo físico:

Este tipo de modelo genérico consiste en dos tablas relacionadas, la primera es llamada “Tipos” la segunda se encuentra relacionada a está con el nombre “Atributos”, esto existe con la razón de que un tipo de entidad tiene una serie de atributos, por ejemplo; si hablamos de un tipo de estado civil, se esta haciendo referencia a 4 atributos: Soltero(a), Casado(a), Viudo(a) y Concubinato, a continuación mostramos la instancias de las tablas mencionadas.

Este tipo de modelo evita crear una tabla por cada “Tipo” implementado en la Base de Datos, ya que pueden existir muchos, pero si se debe crear una vista por cada tipo para obtener los posibles atributos de forma obligatoria, por otro lado se evita la asignación de valores que solo tienen significado ambiguo, el cual puede conllevar a una incorrecta interpretación, por ejemplo:

  • Primer caso: Si hablamos de un tipo llamado genero, es compuesto de los atributos Masculino y Femenino, resulta que para algunos diseñadores o programadores utilizan un valor booleano, el cual le dan su propio significado, true para femenino y false para masculino e incluso al revés. ¿Quien sabe la respuesta?, una posible documentación, una exploración de los datos, o alguien lo comentó, el resultado es el mismo, no existe una información que se encuentra consistente.
  • Segundo caso: Complementando el caso anterior, envés de utilizar valores booleano para definir los atributos, también suelen utilizar de forma incorrecta letras o abreviaturas, como por ejemplo; se utiliza la letra “M” para definir Masculino y “F” para definir Femenino, en otros casos para indicar si una persona es Venezolana colocan “VEN” y si es Extranjera colocan “EXT”.

Utilizar estos mecanismos son imprecisos y generan una serie de inconvenientes, como lo son: Leve aumento del tamaño de la tabla y del tiempo de búsqueda, a pesar que colocan consultas con el uso del CASE para convertir estos valores en “Entendibles”, por lo que es un esfuerzo totalmente impráctico, o mucho peor, la aplicación realiza la interpretación de estos valores.

Con la creación de las tablas atributos & tipos y las respectivas vistas por cada uno de los tipos definidos no es suficiente, recuerden que dentro de la tabla atributos existen una gran cantidad de registros asociados a la tabla tipos, por lo que la tabla atributos a su ves se encuentra relacionada una o varias veces a una misma tabla. Por lo que surge un problema, cada clave foránea puede ser puesta en el sitio incorrecto, es aquí donde se les da uno de los tantos usos a los disparadores o “triggers”, con el fin de poder controlar su integridad.

Ilustramos la instancia de la tabla “usuarios”, con fines didácticos solamente se mostraran las columnas básicas para poder explicar y simplificar la problemática planteada, observe el valores de las claves foráneas y comparelas con las claves primarias definidas anteriormente en la tabla atributos:

En la tabla se ubican cuatro (4) recuadros con el borde de color rojo, cada uno de ellos tiene valores que no corresponden al significado de la columna, por ejemplo: en la columna “fk_nacionalidad” están las clave foráneas 1 y 6, donde el valor 1 hace referencia al atributo Femenino y el 6 al Concubinato, como podemos apreciar no existe ninguna relación conceptual con la Nacionalidad.

A continuación realizaremos un ejemplo básico pero completo, explicado pasos a paso para ir entendiendo, utilizando un modelo simple que casi todo sistema debería utilizar. Primero debemos conocer el modelo físico de la pequeña base de datos que vamos a construir.

Como primer paso vamos a crear tres (3) tablas, una es utilizada para almacenar los usuarios, y las otras dos corresponden al modelo atributos & tipos, por cada tipo crearemos las respectivas vistas y posteriormente implementaremos los triggers en la tabla usuarios para mantener la integridad.

Creación de las tablas:

CREATE TABLE tbl_tipos (
  pk_tipo SERIAL      NOT NULL,
  nombre  VARCHAR(45) NOT NULL,
  CONSTRAINT pk_tipo PRIMARY KEY (pk_tipo),
  CONSTRAINT un_tipo UNIQUE (nombre)
);

CREATE TABLE tbl_atributos (
  pk_atributo SERIAL       NOT NULL,
  fk_tipo     SERIAL       NOT NULL,
  nombre      VARCHAR(255) NOT NULL,
  valor       VARCHAR(8),
  CONSTRAINT pk_atributo PRIMARY KEY (pk_atributo),
  CONSTRAINT tbl_atributos_fk_tipo FOREIGN KEY (fk_tipo)
      REFERENCES tbl_tipos (pk_tipo) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE tbl_usuarios (
  pk_usuario        SERIAL       NOT NULL,
  fk_genero         INTEGER      NOT NULL,
  fk_estado_civil   INTEGER      NOT NULL,
  fk_nacionalidad   INTEGER      NOT NULL,
  identificacion    VARCHAR(10)  NOT NULL,
  nombre            VARCHAR(255) NOT NULL,
  apellido          VARCHAR(255) NOT NULL,
  direccion         TEXT,
  fecha_nacimiento  DATE,
  CONSTRAINT pk_usuario PRIMARY KEY (pk_usuario),
  CONSTRAINT tbl_atributos_fk_genero FOREIGN KEY (fk_genero)
      REFERENCES tbl_atributos (pk_atributo) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tbl_atributos_fk_nacionalidad FOREIGN KEY (fk_nacionalidad)
      REFERENCES tbl_atributos (pk_atributo) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT tbl_atributos_fk_estado_civil FOREIGN KEY (fk_estado_civil)
      REFERENCES tbl_atributos (pk_atributo) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Inserción de los datos de prueba en la tablas Atributos & Tipos:

INSERT INTO tbl_tipos (nombre) VALUES ('Genero');
INSERT INTO tbl_tipos (nombre) VALUES ('Estado Civil');
INSERT INTO tbl_tipos (nombre) VALUES ('Nacionalidad');

INSERT INTO tbl_atributos (fk_tipo, nombre, valor) VALUES (1, 'Masculino'  , 'M');
INSERT INTO tbl_atributos (fk_tipo, nombre, valor) VALUES (1, 'Femenino'   , 'F');
INSERT INTO tbl_atributos (fk_tipo, nombre, valor) VALUES (2, 'Soltero(a)' , null);
INSERT INTO tbl_atributos (fk_tipo, nombre, valor) VALUES (2, 'Casado(a)'  , null);
INSERT INTO tbl_atributos (fk_tipo, nombre, valor) VALUES (2, 'Concubinato', null);
INSERT INTO tbl_atributos (fk_tipo, nombre, valor) VALUES (2, 'Viudo(a)'   , null);
INSERT INTO tbl_atributos (fk_tipo, nombre, valor) VALUES (3, 'Venezolano' , 'V');
INSERT INTO tbl_atributos (fk_tipo, nombre, valor) VALUES (3, 'Extranjero' , 'E');

Creación de las Vistas:

CREATE OR REPLACE VIEW vw_generos AS 
 SELECT pk_atributo, nombre
   FROM tbl_atributos
  WHERE fk_tipo = 1
  ORDER BY nombre;

CREATE OR REPLACE VIEW vw_estados_civiles AS 
 SELECT pk_atributo, nombre
   FROM tbl_atributos
  WHERE fk_tipo = 2
  ORDER BY nombre;

CREATE OR REPLACE VIEW vw_nacionalidades AS 
 SELECT pk_atributo, nombre
   FROM tbl_atributos
  WHERE fk_tipo = 3
  ORDER BY nombre;

Creación del Trigger:

  • Paso 1: Instalar el lenguaje PL/PgSQL, solo en caso de que no se encuentre instalado en la DB:
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
  HANDLER plpgsql_call_handler
  VALIDATOR plpgsql_validator;
  • Paso 2: Creación del trigger:
CREATE OR REPLACE FUNCTION fn_cxux_aev_tbl_usuarios()
  RETURNS trigger AS
$$
BEGIN
  IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN    
    IF (NEW.fk_genero NOT IN (SELECT pk_atributo FROM vw_generos)) THEN
      RAISE EXCEPTION 'No se puede asignar la clave foranea de la columna fk_genero, no corresponde con su rango.';
    END IF;

   IF (NEW.fk_estado_civil NOT IN (SELECT pk_atributo FROM vw_estados_civiles)) THEN
      RAISE EXCEPTION 'No se puede asignar la clave foranea de la columna fk_estad_civil no corresponde con su rango.';
    END IF;

    IF (NEW.fk_nacionalidad NOT IN (SELECT pk_atributo FROM vw_nacionalidades)) THEN
      RAISE EXCEPTION 'No se puede asignar la clave foranea de la columna fk_nacionalidad, no corresponde con su rango.';
    END IF;
    RETURN NEW;
  END IF;

  RETURN NULL;
END;
$$
LANGUAGE 'plpgsql';
  • Paso 3: Crear el trigger que asigna la función “fn_cxux_aev_tbl_usuarios” a la tabla “tbl_usuarios”:
CREATE TRIGGER trg_cux_eav_atributos
  BEFORE INSERT OR UPDATE
  ON tbl_usuarios
  FOR EACH ROW
  EXECUTE PROCEDURE fn_cxux_aev_tbl_usuarios();

Para verificar y demostrar el correcto funcionamiento de toda la estructura que permite mantener correctamente el modelo atributos & tipos, se debe realizar una serie de INSERT sobre la tabla “usuarios” y observar los controles que se aplican.

INSERT INTO tbl_usuarios (fk_genero     , fk_estado_civil, fk_nacionalidad,
                          identificacion, nombre         , apellido)
                  VALUES (1, 3, 7, '123456789', 'Armando Esteban', 'Quito');
INSERT INTO tbl_usuarios (fk_genero     , fk_estado_civil, fk_nacionalidad,
                          identificacion, nombre         , apellido)
                  VALUES (4, 3, 7, '123456789', 'Armando Esteban', 'Quito');

Al ejecutar el segundo insert podemos observar el siguiente mensaje de error:

ERROR:  No se puede asignar la clave foranea de la columna fk_genero, no corresponde con su rango.

Por lo que la validación mediante el uso del trigger se esta efectuando correctamente. Se espera que la explicación y los ejemplos de uso para este patrón les sea de mucha ayuda, a pesar que requiere trabajo, pero como siempre será recompensado a lo largo del tiempo y siéntanse motivados de implementarlo donde quieran.