Buenas a todos.
La verdad es que no me decidía a hacer esta consulta por su, al menos aparente, complejidad, pero dado que he llegado a un "punto muerto" voy a tratar de ser claro.
Básicamente lo que estoy desarrollando es un sistema de gestión en Symfony de artículos y sus características (imaginaos la tabla de artículos de PCBox, aunque no tenga nada que ver con lo que necesito, es por poner un ejemplo rápido).
La complejidad de este sistema está en que las características de cada artículo pueden ser muy numerosas, y lo que es peor, muy variables. Un artículo puede tener, por poner un ejemplo muy básico, las características "color", "tamaño" y "fabricante", y otro artículo sólo "peso". El problema está no sólo en que esas características puedan ser muy numerosas, sino que pueden ser muy flexibles (pueden ser numéricas, texto, fecha, boolean o incluso - lo que más me quita el sueño - claves foráneas de otras tablas).
Es por ello que descarto la idea de introducir las características de un artículo en columnas adicionales dentro de la misma tabla MySQL: primero, porque dichas características son dinámicas (pueden crearse nuevas o eliminar existentes), segundo porque el tamaño de la tabla de artículos podría llegar a ser enorme.
Las alternativas que he buscado son las siguientes:
1. Utilizar MongoDB dado que aparentemente los documentos no siguen ningún esquema específico, pero me tira para atrás que una característica pueda ser clave foránea de otra tabla o documento y no puedan usarse joins. Además de que no soy ningún gurú en MongoDB y no sabría como crear un CRUD de documentos sin esquema.
2. Partir de una tabla muy simple "artículo" (información básica del artículo como nombre y descripción), otra "característica" (título, formato o tipo de datos y descripción, entre otros) y crear una tabla nueva por cada una de las características existentes. Aquí el problema está en: ¿como demonios podría utilizar DDL mediante Doctrine (que a priori pensaba utilizar salvo que me recomendéis lo contrario) para crear o modificar tablas?
También he leído algo de las tablas pivotantes en las cuales se convierten filas en columnas, pero ni idea de si es posible implementar algo de esto mediante Doctrine.
3. Igual que en el caso anterior, pero almacenar las características en una sola columna de la tabla "artículo", almacenándolos en formato json (por ejemplo). El problema es: para el formulario de artículo ¿cómo puedo implementar el campo de características si este es variable y dinámico?
4. Partir de las tablas de la solución 2 y crear otra "artículo_característica" para la relación n-n de modo que cada fila conste de [artículo_id, característica_id, valor]. De momento es la opción que más valoro, pero me tira para atrás que el campo "valor" sea tan variable en tipo y tamaño: almacenar en la misma columna textos tanto cortos como largos, enteros, fechas o lo que es peor como ya he dicho anteriormente, claves foráneas, me parece pasarse por el forro la normalización, además de que estoy seguro que afectaría al rendimiento.
¿Qué opináis vosotros?
Aunque realmente me he dejado cosas en el tintero creo que mi problema de base lo he podido explicar. Lo importante es que pueda "tirar para adelante" siguiendo las mejores prácticas en cuanto a implementación de BBDD.
Respuestas
De los cuatro escenarios que planteas como solución, en mi opinión los mejores serían el primero y el último.
Lo malo de la opción 1 es que dices que no tienes experiencia con MongoDB, por lo que te exigirá un esfuerzo no despreciable sólo para poder empezar. Y cuando somos nuevos en alguna tecnología, ya sabes lo fácil que es cometer errores de principiante.
La solución 4 creo que es la más correcta desde el punto de vista técnico. De hecho, este tipo de modelos se llaman EAV, del inglés Entity - Attribute - Value y funcionan exactamente como propones en tu pregunta. Un ejemplo muy conocido de aplicación que usa este modelo es WordPress, que permite definir un número ilimitado de atributos de cualquier tipo para sus posts y páginas.
@javiereguiluz
Pues la verdad es que me aportas mucha luz al asunto porque mira que he tratado de buscar este tipo de modelos y sus soluciones y lo único que he encontrado es lo de las pivot tables.
En cuanto a la opción MongoDB, no temo utilizarlo porque ya tengo cierto conocimiento, sin embargo donde flaqueo más es en utilizar todo su potencial a través de Doctrine, como ya he comentado, crear un modelo tan dinámico creo que no me resultará nada sencillo.
Respecto a la opción 4, tengo que confesar que ya había realizado una aplicación mediante este método, la duda es si lo tenía bien optimizado e implementado utilizando como columna "valor" un tipo varchar (almacenando en el mismo tanto enteros, booleanos, textos cortos/largos, fechas, etc...) y de ahí viene mi consulta.
En cualquier caso creo que voy bien encaminado porque las alternativas más barajadas por mi parte eran precisamente esas, especialmente la última; ahora sólo tengo que implementarlas correctamente si doctrine me lo permite :)
Muchas gracias por tu ayuda!
@KePitt2
Otro ejemplo práctico de cómo hacer EAV con PHP es Magento, la famosa aplicación de comercio electrónico. Aquí puedes echar un vistazo al diagrama y a los detalles de su base de datos.
@javiereguiluz
Muchas gracias Javier! Ya estuve investigando ayer un buen rato. En este tutorial se explica también de manera simple...
La solución que adopta Magento, en resumidas cuentas, es utilizar tablas distintas para cada tipo de valor en lugar de una sóla tabla de valores tal y como lo llevo haciendo hasta ahora utilizando una única columna "valor" de tipo VARCHAR(4000); es decir, en magento, además de las tablas de entidades y atributos, hay una tabla para valores de text, otra para valores varchar, otra para datetime, etc). En mi caso sería algo como:
artículo_características_values_varchar:
| Columnas | Tipos |
|---|---|
| entidad_id | INT |
| atributo_id | INT |
| value | VARCHAR(255) |
artículo_características_values_datetime:
| Columnas | Tipos |
|---|---|
| entidad_id | INT |
| atributo_id | INT |
| value | DATETIME |
etc...
No niego que la solución sea la más correcta, pero la complejidad a la hora de hacer consultas es mucho más elevada.
Yo propondría una solución intermedia: utilizar una única tabla de valores, creando una columna para cada tipo de datos. No se si sería más óptimo porque en cada tupla habría campos vacíos (únicamente tendría valor el campo cuyo tipo de datos coincida con el del atributo). Es un p*** lío pero sería algo como:
artículo_características_values:
| Columnas | Tipos |
|---|---|
| entidad_id | INT |
| atributo_id | INT |
| value_varchar | VARCHAR(255) |
| value_text | TEXT |
| value_datetime | DATETIME |
| value_integer | INT |
| ... | ... |
Por cuestiones de tamaño no debería dar problemas, pero dejamos toda la lógica de consultas a la propia aplicación que sería algo más sencilla.
@KePitt2
Como tú mismo dices, la solución de Magento sería más correcta técnicamente ... pero ellos tienen un equipo de desarrollo enorme que lleva años trabajando en esto. Si los recursos son limitados y la aplicación que tienes que desarrollar no es tan compleja, puede ser un compromiso aceptable hacer lo que propones.
@javiereguiluz
En principio lo haré así. La crema sería que hubiera un Bundle en Symfony que implementara este modelo. Si me voy desenvolviendo bien con el tema igual me pongo a ello... Me parece un tema bastante interesante.
@KePitt2