sentencias - comandos sql

¿Cómo diseñar un esquema de base de datos para admitir el etiquetado con categorías? (5)

Estoy tratando de algo así como el Diseño de la base de datos para el etiquetado , excepto que cada una de mis etiquetas se agrupan en categorías.

Por ejemplo, digamos que tengo una base de datos sobre vehículos. Digamos que en realidad no sabemos mucho sobre vehículos, por lo que no podemos especificar las columnas que tendrán todos los vehículos. Por lo tanto, "etiquetaremos" vehículos con información.

1. manufacture: Mercedes model: SLK32 AMG convertible: hardtop 2. manufacture: Ford model: GT90 production phase: prototype 3. manufacture: Mazda model: MX-5 convertible: softtop

Ahora, como puede ver, todos los automóviles están etiquetados con su fabricación y modelo, pero las otras categorías no coinciden. Tenga en cuenta que un automóvil solo puede tener uno de cada categoría. ES DECIR. Un automóvil solo puede tener un fabricante.

Quiero diseñar una base de datos para apoyar una búsqueda de todos los Mercedes, o para poder enumerar todos los fabricantes.

Mi diseño actual es algo como esto:

vehicles int vid String vin vehicleTags int vid int tid tags int tid String tag int cid categories int cid String category

Tengo todas las llaves primarias y extranjeras correctas en su lugar, excepto que no puedo manejar el caso donde cada automóvil solo puede tener un fabricante. ¿O puedo?

¿Puedo agregar una restricción de clave externa a la clave primaria compuesta en etiquetas de vehículo? ES DECIR. ¿Podría agregar una restricción tal que la clave primaria compuesta (vid, tid) solo se pueda agregar a los Títulos de vehículo solo si no hay una fila en los Títulos de vehículo, de modo que para el mismo video, todavía no hay un tid en el el mismo cid?

Mi conjetura es no. Creo que la solución a este problema es agregar una columna cid a vehicleTags, y crear la nueva clave primaria compuesta (vid, cid). Se vería así:

vehicleTags int vid int cid int tid

Esto evitaría que un automóvil tenga dos fabricantes, pero ahora he duplicado la información de que el tid está en cid.

¿Cuál debería ser mi esquema?

Tom notó este problema en mi esquema de base de datos en mi pregunta anterior, ¿Cómo se hacen muchas combinaciones externas de tabla?

EDITAR
Sé que en el ejemplo, la fabricación debería ser realmente una columna en la mesa del vehículo, pero digamos que no se puede hacer eso. El ejemplo es solo un ejemplo.

Una forma sería repensar un poco su esquema, normalizando las claves de los valores:

vehicles int vid string vin tags int tid int cid string key categories int cid string category vehicleTags int vid int tid string value

Ahora todo lo que necesita es una restricción única en vehicleTags(vid, tid) .

Alternativamente, hay formas de crear restricciones más allá de las claves externas simples: dependiendo de su base de datos, ¿puede escribir una restricción personalizada o un activador de inserción / actualización para imponer la exclusividad de la etiqueta del vehículo?

Creo que su solución es simplemente agregar una columna de fabricante a su tabla de vehículos. Es un atributo que usted sabe que todos los vehículos tendrán (es decir, los automóviles no aparecen espontáneamente por sí mismos) y al convertirlo en una columna en la tabla de su vehículo, usted resuelve el problema de tener un solo fabricante para cada vehículo. Este enfoque se aplicaría a cualquier atributo que sepa que será compartido por todos los vehículos. Luego puede implementar el sistema de etiquetado para los otros atributos que no son universales.

Así que tomando de su ejemplo, la mesa del vehículo sería algo así como:

vehicle vid vin make model

Esta es otra variación más en el diseño Entity-Attribute-Value .

Una tabla EAV más reconocible se ve así:

CREATE TABLE vehicleEAV ( vid INTEGER, attr_name VARCHAR(20), attr_value VARCHAR(100), PRIMARY KEY (vid, attr_name), FOREIGN KEY (vid) REFERENCES vehicles (vid) );

Algunas personas obligan a attr_name a hacer referencia a una tabla de búsqueda de nombres de atributos predefinidos, para limitar el caos.

Lo que has hecho es simplemente extender una tabla EAV en tres tablas, pero sin mejorar el orden de tus metadatos:

CREATE TABLE vehicleTag ( vid INTEGER, cid INTEGER, tid INTEGER, PRIMARY KEY (vid, cid), FOREIGN KEY (vid) REFERENCES vehicles(vid), FOREIGN KEY (cid) REFERENCES categories(cid), FOREIGN KEY (tid) REFERENCES tags(tid) ); CREATE TABLE categories ( cid INTEGER PRIMARY KEY, category VARCHAR(20) -- "attr_name" ); CREATE TABLE tags ( tid INTEGER PRIMARY KEY, tag VARCHAR(100) -- "attr_value" );

Si vas a utilizar el diseño EAV, solo necesitas el vehicleTags Tablas y tablas de categories .

CREATE TABLE vehicleTag ( vid INTEGER, cid INTEGER, -- reference to "attr_name" lookup table tag VARCHAR(100, -- "attr_value" PRIMARY KEY (vid, cid), FOREIGN KEY (vid) REFERENCES vehicles(vid), FOREIGN KEY (cid) REFERENCES categories(cid) );

Pero tenga en cuenta que está mezclando datos con metadatos . Pierdes la habilidad de aplicar ciertas restricciones a tu modelo de datos.

¿Cómo puede hacer que una de las categorías sea obligatoria (una columna convencional usa una restricción NOT NULL )?
¿Cómo puede usar los tipos de datos SQL para validar algunos de sus valores de etiqueta? No puedes, porque estás usando una cadena larga para cada valor de etiqueta. ¿Esta cadena es lo suficientemente larga para cada etiqueta que necesitarás en el futuro? No puedes decir.
¿Cómo puede restringir algunas de sus etiquetas a un conjunto de valores permitidos (una tabla convencional utiliza una clave externa para una tabla de búsqueda)? Este es su ejemplo "softtop" vs. "soft top". Pero no puede establecer una restricción en la columna de tag porque esa restricción se aplicaría a todos los demás valores de etiquetas para otras categorías. También debe restringir el tamaño del motor y el color de la pintura a la "parte superior blanda".

Las bases de datos SQL no funcionan bien con este modelo. Es extremadamente difícil acertar, y consultarlo se vuelve muy complejo. Si continúa utilizando SQL, será mejor que modele las tablas de manera convencional, con una columna por atributo. Si necesita tener "subtipos", defina una tabla subordinada por subtipo ( herencia de tabla de clase ) o use herencia de tabla única . Si tiene una variación ilimitada en los atributos por entidad, utilice el LOB serializado .

Otra tecnología que está diseñada para este tipo de modelos de datos fluidos y no relacionales es una Base de datos semántica que almacena datos en RDF y se consulta con SPARQL . Una solución gratuita es Sesame .

Necesitaba resolver este problema exacto (el mismo dominio general y todo, partes de automóviles). Descubrí que la mejor solución al problema era usar Lucene / Xapian / Ferret / Sphinx o cualquier indexador de texto completo que prefiriera. Mucho mejor desempeño que lo que SQL puede ofrecer.

Hoy en día, casi nunca termino construyendo una aplicación web respaldada por una base de datos que no involucre un indexador de texto completo. Este problema y el problema general de la búsqueda surgen con demasiada frecuencia para omitir los indizadores de su caja de herramientas.