Relación de muchos a muchos: ¿usar tabla asociativa o valores delimitados en una columna?
Update 2009.04.24
El punto principal de mi pregunta no es la confusión del desarrollador y qué hacer al respecto.
El punto es entender cuándo los valores delimitados son la solución correcta.
He visto datos delimitados utilizados en bases de datos de productos comerciales (Ektron lol).
SQL Server incluso tiene un tipo de datos XML, por lo que podría usarse para el mismo propósito que los campos delimitados.
/ end Update
La aplicación que estoy diseñando tiene algunas relaciones de muchos a muchos. En el pasado, solía usar tablas asociativas para representarlas en la base de datos. Esto ha causado cierta confusión a los desarrolladores.
Aquí hay una estructura de base de datos de ejemplo:
Document
---------------
ID (PK)
Title
CategoryIDs (varchar(4000))
Category
------------
ID (PK)
Title
Hay una relación de muchos a muchos entre Documento y Categoría.
En esta implementación, Document.CategoryIDs es una gran lista delimitada por tuberías de CategoryID.
Para mí, esto es malo porque requiere el uso de la coincidencia de subcadenas en las consultas, que no pueden hacer uso de índices. Creo que esto será lento y no escalará.
Con ese modelo, para obtener todos los documentos para una categoría, necesitaría algo como lo siguiente:
select * from documents where categoryids like '%|' + @targetCategoryId + '|%'
Mi solución es crear una tabla asociativa de la siguiente manera:
Document_Category
-------------------------------
DocumentID (PK)
CategoryID (PK)
Esto es confuso para los desarrolladores. ¿Hay alguna solución alternativa elegante que me falta?
Supongo que habrá miles de filas en el documento. La categoría puede ser como 40 filas más o menos. La principal preocupación es el rendimiento de la consulta. ¿Estoy sobre ingeniería esto?
¿Hay algún caso en el que se prefiera almacenar listas de ID en columnas de la base de datos en lugar de enviar los datos a una tabla asociativa?
Considere también que es posible que necesitemos crear relaciones de muchos a muchos entre los documentos. Esto sugeriría una tabla asociativa Document_Document. ¿Es ese el diseño preferido o es mejor almacenar las ID de documento asociadas en una sola columna?
Gracias