Supongamos que está recopilando información privilegiada sobre los próximos estrenos de películas de superhéroes y su tabla principal de películas se ve algo así:

tabla 1

<code>Title              Director   Leading Male      Leading Female    Villain
--------------------------------------------------------------------------
Green Lantern      Kubrick    Robert Redford     Miley Cyrus     Hugh Grant  
The Tick          Mel Gibson  Kevin Sorbo        Linda Hunt    Anthony Hopkins
</code>

Esto debería funcionar muy bien en general y permitir consultas muy fáciles, así como comparaciones entre filas.

Sin embargo, le gustaría rastrear la fuente de cada hecho de los datos, así como el nombre del periodista que lo descubrió. Esto parece sugerir algún tipo deEAV mesa como esta:

Tabla 2

<code>Movie             Attribute            Value          Source          Journalist
----------------------------------------------------------------------------------
Green Lantern      Director           Kubrick         CHUD              Sarah
Green Lantern    Leading Male      Robert Redford     CHUD              James
Green Lantern   Leading Female      Miley Cyrus    Dark Horizons        James
Green Lantern      Villain           Hugh Grant       CHUD              Sarah
The Tick           Director          Mel Gibson       Yahoo            Cameron
...
</code>

Lo que, si bien captura fácilmente los metadatos que queríamos, hace que las consultas sean más difíciles. Se necesita un poco más para simplemente obtener todos los datos básicos de una sola película. Más específicamente, tienes que lidiar con cuatro filas aquí para obtener los cuatro datos importantes de la Linterna Verde, mientras que en la tabla 1 es una fila única, bien encapsulada.

Entonces, mi pregunta es, a la luz de las complicaciones que acabo de describir, y porque sé que en general se deben evitar las tablas de EAV, ¿sigue siendo la mejor solución el EAV? Parece que es la única forma razonable de representar estos datos. La única otra alternativa que veo es usar la tabla 1 junto con otra quesolamente alberga metadatos como este:

Tabla 3

<code>Movie             Attribute            Source          Journalist
----------------------------------------------------------------------------------
Green Lantern      Director             CHUD              Sarah
Green Lantern    Leading Male           CHUD              James
Green Lantern   Leading Female      Dark Horizons         James
Green Lantern      Villain              CHUD              Sarah
The Tick           Director             Yahoo            Cameron
...
</code>

Pero esto es muy peligroso porque si alguien cambia el nombre de una columna en la tabla 1, como "Villano" a "Villano primario", la fila de la tabla 3 seguirá diciendo simplemente "Villano" y, por lo tanto, los datos relacionados se desacoplarán. Esto podría ser útil si la columna de "atributo" se vinculara a otra tabla que sirviera como una enumeración de las columnas de la tabla 1. Por supuesto, el DBA sería responsable de mantener esta tabla de enumeración para que coincida con las columnas reales de la tabla 1. Y en realidad podría ser posible mejorar esto aún más en lugar de crear la tabla de enumeración a mano, use una vista del sistema en SQL Server que aloja los nombres de las columnas en la tabla 1. Aunque no estoy seguro de que pueda tener relaciones que involucren Vistas del sistema.

¿Que sugieres? ¿Es el EAV el único camino a seguir?

¿Y qué pasaría si solo se tratara de una columna de metadatos (solo "Fuente" sin "Periodista")? ¿Es necesario seguir la ruta EAV? Podría tener las columnas "Director", "Director_Source", "Leading Male", "Leading Male_Source", etc., pero eso se pone feo muy rápidamente. ¿Hay alguna solución mejor que no estoy pensando?

Si no he aclarado algún punto, por favor comente y agregaré más según sea necesario. Ah, sí, y los datos de la película que utilicé están fabricados :)

Edición: Para reformular mi pregunta principal de manera concisa, me gustaría tener la simplicidad y el verdadero diseño de RDBMS de la tabla 1, que realmente describe bien una entrada de película, a la vez que almacena los metadatos de los atributos de una manera segura y accesible. es posible? ¿O es EAV la única manera?

Edición 2: después de investigar un poco más en la web, aún tengo que encontrar una discusión sobre EAV que se centre en el deseo de almacenar metadatos en las columnas. La razón principal dada para implementar un EAV es casi siempre columnas dinámicas e impredecibles, que no es el caso en mi ejemplo. En mi ejemplo, siempre hay las mismas cuatro columnas: director, líder masculino, líder femenino, villano. Sin embargo, quiero almacenar ciertos hechos (fuente y periodista) sobre cada columna para cada fila. Un EAV facilitaría esto, pero me gustaría evitar recurrir a eso.

Actualizar

Usando el diseño de la Tabla 2, excepto para cambiar el nombre de la columna "Película" a "Nombre" y llamar a la tabla completa "Película", aquí está la operación pivote en SQL Server 2008 para volver a la Tabla 1:

<code>SELECT Name, [Director], [Leading Male], [Leading Female], [Villain]
FROM (Select Name, Attribute, Value FROM Movie) as src
PIVOT
(
Max(Value)
FOR Attribute IN ([Director], [Leading Male], [Leading Female], [Villain])
)  AS PivotTable
</code>