¡Hola, chicos!
En este artículo te demostraré qué es una operación bit a bit, disponible desde SQL Server 2005, y cómo almacenar varios valores distintos en una misma columna, que puede incluso tener una combinación de valores, sin necesidad de crear una tabla N:N para almacenar las combinaciones.

He visto en algunos sistemas este enfoque de usar operaciones bit a bit para almacenar varios valores en una misma columna y siempre me he preguntado por qué no crear una tabla N:N para esto, ya que es mucho más simple y también permite la indexación. Después de responder algunas preguntas en Telegram sobre operaciones bit a bit, decidí escribir este artículo para demostrar estas dos formas de implementar una relación multivalor entre dos tablas, aunque no es un escenario muy común de encontrar.

En un escenario rápido para ejemplificar un uso interesante de este recurso, imaginemos una estructura para almacenar qué tecnologías domina cada persona, que podrían ser solo una o varias tecnologías para una misma persona.

Script para crear tablas de ejemplo:

IF (OBJECT_ID('tempdb..#Profissionais') IS NOT NULL) DROP TABLE #Profissionais
CREATE TABLE #Profissionais (
    Id_Pessoa INT IDENTITY(1,1),
    Nome VARCHAR(60)
)

IF (OBJECT_ID('tempdb..#Skills') IS NOT NULL) DROP TABLE #Skills
CREATE TABLE #Skills (
    Id_Habilidade INT IDENTITY(1,1),
    Skill VARCHAR(60)
)

INSERT INTO #Profissionais (Nome)
VALUES
    ('Dirceu'), ('Fabrício'), ('Tiago'), ('Luiz'), ('Alan'),
    ('Rafael'), ('Carol'), ('Leandro'), ('Fabiana'), ('Danyele'),
    ('Diego'), ('???? rs')


INSERT INTO #Skills ( Skill )
VALUES
    ('SQL'), ('C#'), ('PHP'), ('Java'), ('Javascript'),
    ('C'), ('R'), ('Python'), ('GO'), ('HTML'), ('CSS')

Estructura propuesta:

Escenario 1: uso de la tabla N:N

Cuando necesitas que una entidad tenga más de un valor, una estrategia comúnmente utilizada es crear una tabla N:N para relacionar las 2 entidades:

IF (OBJECT_ID('tempdb..#Profissionais_Skills') IS NOT NULL) DROP TABLE #Profissionais_Skills
CREATE TABLE #Profissionais_Skills (
    Id_Pessoa INT,
    Id_Habilidade INT
)

INSERT INTO #Profissionais_Skills ( Id_Pessoa, Id_Habilidade )
VALUES
    (1, 1), (1, 2), (1, 3), (1, 5), (1, 10), (1, 11)

Y teniendo la siguiente configuración:

En otras palabras, relacioné el ID de las personas con el ID de la habilidad. Para cada combinación deseada, agregaremos un registro para crear esta relación entre las 2 entidades. Para esta solución fue necesario crear una nueva tabla para esta relación, pero la visualización de la información es muy fácil de buscar y filtrar. En cuanto al rendimiento, podemos crear un índice para facilitar las búsquedas, si fuera necesario.

Si quiero identificar quiénes son las personas que tienen un determinado Skill, simplemente filtro por el ID del Skill que quiero:

Escenario 2: uso de operaciones bit a bit

Entrando al mundo de las operaciones bit a bit, podemos crear una solución para almacenar múltiples valores sin necesidad de crear una tabla N:N para ello, almacenando los múltiples valores en una misma columna (y sin la molestia de los datos serializados).

Para que esto sea posible, primero que nada, necesito crear una nueva columna en la tabla Profesionales para almacenar estos múltiples valores. Y será una columna de tipo INT (así es... jajaja):

ALTER TABLE #Profissionais ADD Ids_Skill INT

Además, nuestra estructura de Habilidades necesitará algunos cambios, porque necesito que los ID funcionen como potencias de 2, es decir, 1, 2, 4, 8, 16, 32, 64, 128...

ALTER TABLE #Skills ADD Id_Habilidade_Nova AS (POWER(2, Id_Habilidade - 1))

Resultado:

Usando esta nueva columna creada (Id_Skill_New), las habilidades ahora se asociarán con las personas. agregando los ID de habilidades y almacenar el valor total en la columna Ids_Skill de la tabla #Profesionales.

Ejemplo:
En este ejemplo, asociaré las mismas Skills del escenario 1 (SQL, C#, PHP, Javascript, HTML y CSS) a la persona Dirceu:

UPDATE #Profissionais
SET Ids_Skill = (1 + 2 + 4 + 16 + 512 + 1024)
WHERE Id_Pessoa = 1 -- Dirceu

-- 1     SQL
-- 2     C#
-- 4     PHP
-- 8     Java
-- 16    Javascript
-- 32    C
-- 64    R
-- 128   Python
-- 256   GO
-- 512   HTML
-- 1024  CSS

Por supuesto, en este enfoque bit a bit, la gestión acaba siendo mucho más laboriosa, pero utilizando una interfaz de aplicación, esto acaba siendo transparente para el usuario.

Para identificar las Skills que tiene esta persona, podemos utilizar operaciones bit a bit (&), que básicamente hace una comparación binaria entre 2 valores:

DECLARE @Numero INT = (SELECT Ids_Skill FROM #Profissionais WHERE Nome = 'Dirceu')

IF ((1 & @Numero) = 1) PRINT 'Tem SQL'
IF ((2 & @Numero) = 2) PRINT 'Tem C#'
IF ((4 & @Numero) = 4) PRINT 'Tem PHP'
IF ((8 & @Numero) = 8) PRINT 'Tem Java'
IF ((16 & @Numero) = 16) PRINT 'Tem Javascript'
IF ((32 & @Numero) = 32) PRINT 'Tem C'
IF ((64 & @Numero) = 64) PRINT 'Tem R'
IF ((128 & @Numero) = 128) PRINT 'Tem Python'
IF ((256 & @Numero) = 256) PRINT 'Tem GO'
IF ((512 & @Numero) = 512) PRINT 'Tem HTML'
IF ((1024 & @Numero) = 1024) PRINT 'Tem CSS'

Resultado:

Y para identificar las personas en la tabla que tienen el Skill PHP (Id = 3, Id_Habilidade_Nova = 4):

SELECT * 
FROM #Profissionais
WHERE ((4 & Ids_Skill) = 4) -- PHP

Resultado:

Si desea traer una lista de personas y habilidades, tal como lo hicimos en el escenario 1 a través de JOINS simples, bueno... la tarea usando la arquitectura bit a bit será mucho más compleja que eso...

Ventajas y desventajas de utilizar operaciones bit a bit en esta situación

Después de demostrar las formas más comunes de asignar múltiples valores entre 2 entidades, también demostraré las ventajas y desventajas de usar operaciones bit a bit en esta situación.

Ventajas

  • No es necesario crear otra tabla solo para almacenar la relación entre las 2 entidades.

Desventajas

  • Mucho más complejo de entender
  • La indexación no es eficiente, ya que se tendría que crear 1 columna calculada para cada atributo con la operación bit a bit e indexar cada columna calculada.
  • Es más complejo auditar cada relación creada entre las 2 tablas, ya que el mismo registro se actualiza varias veces. En el escenario N:N, cada registro podría tener la fecha de creación y el usuario que lo creó.
  • Cada vez que se crea/cambia/elimina una relación, es necesario volver a calcular el valor de esa columna en lugar de simplemente eliminar 1 registro.
  • Traer una relación entre personas y habilidades es una tarea muy laboriosa
  • La creación de una nueva Skill en el banco podría requerir algunos cambios en el sistema

Como se demostró anteriormente, esta solución de uso bit a bit termina trayendo muchas más desventajas que ventajas (si esta “ventaja” es significativa). Realmente prefiero usar una tabla N:N para almacenar relaciones multivalor, tanto por rendimiento como por practicidad.

Referencias:
– https://www.mssqltips.com/sqlservertip/1218/sql-server-bitwise-operators-to-store-multiple-values-in-one-column/
– https://www.sqlservercentral.com/articles/introduction-to-bitmasking-in-sql-server-2005
– https://www.red-gate.com/simple-talk/sql/t-sql-programming/bitwise-operatives-in-t-sql/

Bueno, espero que hayas disfrutado este artículo y hayas aprendido algo nuevo para tu vida diaria 🙂
¡Un abrazo grande y hasta la próxima!