diseño de base de datos para contener información de metadatos diferente

Así que estoy tratando de diseñar una base de datos que me permita conectar un producto con múltiples categorías. Esta parte que he figurado. Pero lo que no puedo resolver es la cuestión de tener diferentes tipos de detalles del producto.

Por ejemplo, el producto podría ser un libro (en cuyo caso necesitaría metadatos que se refieran a ese libro como isbn, autor, etc.) o podría ser un listado de empresas (que tiene metadatos diferentes).

¿Cómo debería abordar eso?

Esto se llama patrón de observación.

enter image description here

Tres objetos, por ejemplo

Book Title = 'Gone with the Wind' Author = 'Margaret Mitchell' ISBN = '978-1416548898' Cat Name = 'Phoebe' Color = 'Gray' TailLength = 9 'inch' Beer Bottle Volume = 500 'ml' Color = 'Green' 

Así es como se ven las tablas:

 Entity EntityID Name Description 1 'Book' 'To read' 2 'Cat' 'Fury cat' 3 'Beer Bottle' 'To ship beer in' 

.

 PropertyType PropertyTypeID Name IsTrait Description 1 'Height' 'NO' 'For anything that has height' 2 'Width' 'NO' 'For anything that has width' 3 'Volume' 'NO' 'For things that can have volume' 4 'Title' 'YES' 'Some stuff has title' 5 'Author' 'YES' 'Things can be authored' 6 'Color' 'YES' 'Color of things' 7 'ISBN' 'YES' 'Books would need this' 8 'TailLength' 'NO' 'For stuff that has long tails' 9 'Name' 'YES' 'Name of things' 

.

 Property PropertyID EntityID PropertyTypeID 1 1 4 -- book, title 2 1 5 -- book, author 3 1 7 -- book, isbn 4 2 9 -- cat, name 5 2 6 -- cat, color 6 2 8 -- cat, tail length 7 3 3 -- beer bottle, volume 8 3 6 -- beer bottle, color 

.

 Measurement PropertyID Unit Value 6 'inch' 9 -- cat, tail length 7 'ml' 500 -- beer bottle, volume 

.

 Trait PropertyID Value 1 'Gone with the Wind' -- book, title 2 'Margaret Mitchell' -- book, author 3 '978-1416548898' -- book, isbn 4 'Phoebe' -- cat, name 5 'Gray' -- cat, color 8 'Green' -- beer bottle, color 

EDITAR:

Jefferey planteó un punto válido (ver comentario), por lo que ampliaré la respuesta.

El modelo permite la creación dinámica (en vuelo) de cualquier cantidad de entidades con cualquier tipo de propiedades sin cambios de esquema. Hovewer, esta flexibilidad tiene un precio: el almacenamiento y la búsqueda son más lentos y más complejos que en un diseño de mesa habitual.

Es hora de dar un ejemplo, pero primero, para facilitar las cosas, voy a aplanar el modelo a una vista.

 create view vModel as select e.EntityId , x.Name as PropertyName , m.Value as MeasurementValue , m.Unit , t.Value as TraitValue from Entity as e join Property as p on p.EntityID = p.EntityID join PropertyType as x on x.PropertyTypeId = p.PropertyTypeId left join Measurement as m on m.PropertyId = p.PropertyId left join Trait as t on t.PropertyId = p.PropertyId ; 

Para usar el ejemplo de Jefferey del comentario

 with q_00 as ( -- all books select EntityID from vModel where PropertyName = 'object type' and TraitValue = 'book' ), q_01 as ( -- all US books select EntityID from vModel as a join q_00 as b on b.EntityID = a.EntityID where PropertyName = 'publisher country' and TraitValue = 'US' ), q_02 as ( -- all US books published in 2008 select EntityID from vModel as a join q_01 as b on b.EntityID = a.EntityID where PropertyName = 'year published' and MeasurementValue = 2008 ), q_03 as ( -- all US books published in 2008 not discontinued select EntityID from vModel as a join q_02 as b on b.EntityID = a.EntityID where PropertyName = 'is discontinued' and TraitValue = 'no' ), q_04 as ( -- all US books published in 2008 not discontinued that cost less than $50 select EntityID from vModel as a join q_03 as b on b.EntityID = a.EntityID where PropertyName = 'price' and MeasurementValue < 50 and MeasurementUnit = 'USD' ) select EntityID , max(case PropertyName when 'title' than TraitValue else null end) as Title , max(case PropertyName when 'ISBN' than TraitValue else null end) as ISBN from vModel as a join q_04 as b on b.EntityID = a.EntityID group by EntityID ; 

Parece complicado de escribir, pero en una inspección más cercana puede notar un patrón en CTE.

Ahora supongamos que tenemos un diseño de esquema fijo estándar donde cada propiedad de objeto tiene su propia columna. La consulta sería algo así como:

 select EntityID, Title, ISBN from vModel WHERE ObjectType = 'book' and PublisherCountry = 'US' and YearPublished = 2008 and IsDiscontinued = 'no' and Price < 50 and Currency = 'USD' ; 

No iba a responder, pero ahora la respuesta aceptada tiene una muy mala idea. Una base de datos relacional nunca debe usarse para almacenar pares simples de atributo-valor. Eso causará muchos problemas en el futuro.

La mejor forma de lidiar con esto es crear una tabla separada para cada tipo.

 Product ------- ProductId Description Price (other attributes common to all products) Book ---- ProductId (foreign key to Product.ProductId) ISBN Author (other attributes related to books) Electronics ----------- ProductId (foreign key to Product.ProductId) BatteriesRequired etc. 

Cada fila de cada tabla debe representar una proposición sobre el mundo real, y la estructura de las tablas y sus restricciones deben reflejar las realidades que se están representando. Cuanto más se acerque a este ideal, más limpios serán los datos y más fácil será informar y ampliar el sistema de otras maneras. También funcionará más eficientemente.

Podría ir con el enfoque sin esquema:

Mantenga los metadatos en una columna de TEXTO como un objeto JSON (u otra serialización, pero JSON es mejor por razones que se explican a continuación).

Ventajas de esta técnica:

  1. Menos consultas: obtienes toda la información en una consulta, no hay necesidad de consultas “en dirección” (para obtener meta-meta-datos) y se une.

  2. Puede agregar / eliminar cualquier atributo que desee en cualquier momento, sin necesidad de modificar la tabla (lo cual es problemático en algunas bases de datos, por ejemplo, Mysql bloquea la tabla, y lleva mucho tiempo con tablas enormes)

  3. Como es JSON, no necesita procesamiento adicional en su back-end. Su página web (supongo que es una aplicación web) simplemente lee el JSON como está de su servicio web y eso es todo, puede usar el objeto JSON con javascript como desee.

Problemas:

  1. Espacio potencialmente desperdiciado, si tiene 100 libros con el mismo autor, una tabla de autor con todos los libros que tienen solo el author_id es más económico en términos de espacio.

  2. Necesidad de implementar índices. dado que sus metadatos son un objeto JSON, no tiene índices de inmediato. Pero es bastante fácil implementar un índice específico para los metadatos específicos que necesita. Por ejemplo, si desea indexar por autor, de modo que cree una tabla author_idx con author_id y item_id, cuando alguien busque un autor, puede buscar esta tabla y los elementos en sí mismos.

Dependiendo de la escala, esto podría ser una exageración. en combinaciones de menor escala funcionaría bien.

El producto debe ser typescript. por ejemplo, incluir type_id en la tabla de productos, que apunta a las categorías de productos que admitirá, y le permite saber en qué otras tablas consultar para los atributos relacionados correspondientes.

En este tipo de problema, tiene tres opciones:

  1. Crea una tabla con columnas “genéricas”. Por ejemplo, si vende tanto libros como tostadoras, es probable que sus tostadoras no tengan un ISBN y un título, pero todavía tienen algún tipo de identificador y descripción del producto. Así que déle a los campos nombres generics como “product_id” y “description”, y para libros, el product_id es un ISBN, para tostadores es el número de parte del fabricante, etc.

Esto funciona cuando las entidades del mundo real se procesan todas de la misma manera, al menos en su mayor parte, y deben tener, si no los “mismos” datos, al menos datos análogos. Esto se rompe cuando hay diferencias funcionales reales. Como si para los tostadores estuviéramos calculando vatios = voltios * amperios, es probable que no haya un cálculo correspondiente para los libros. Cuando comienza a crear un campo pages_volts que contiene el recuento de páginas para libros y el voltaje para tostadores, las cosas se han salido de control.

  1. Use un esquema de propiedad / valor como sugiere Damir. Vea mi comentario sobre su publicación para conocer los pros y contras de allí.

  2. Lo que generalmente sugiero es un esquema de tipo / subtipo. Cree una tabla para “producto” que contenga un código de tipo y los campos generics. Luego, para cada uno de los tipos verdaderos (libros, tostadores, gatos, lo que sea), cree una tabla separada que esté conectada a la tabla de productos. Luego, cuando necesite hacer un procesamiento específico del libro, procese la tabla del libro. Cuando necesite hacer un procesamiento genérico, procese la tabla de productos.