sql - tabla - Esquema de base de datos dinámica

query dinamico sql server

Question

¿Cuál es una arquitectura recomendada para proporcionar almacenamiento para un esquema de base de datos lógica dinámica?

Para aclarar: cuando se requiere que un sistema proporcione almacenamiento para un modelo cuyo esquema puede ser ampliado o alterado por sus usuarios una vez en producción, ¿cuáles son algunas buenas tecnologías, modelos de bases de datos o motores de almacenamiento que lo permitan?

Algunas posibilidades para ilustrar:

• Crear / alterar objetos de base de datos a través de DML generado dinámicamente
• Crear tablas con grandes cantidades de columnas físicas dispersas y usar solo aquellas requeridas para el esquema lógico ''superpuesto''
• Crear una tabla ''larga y estrecha'' que almacene los valores dinámicos de las columnas como filas que luego se deben pivotar para crear un conjunto de filas ''corto y ancho'' que contenga todos los valores para una entidad específica
• Usar un sistema de tipo BigTable / SimpleDB PropertyBag

Cualquier respuesta basada en la experiencia del mundo real sería muy apreciada

Answer 1

Crea 2 bases de datos

• DB1 contiene tablas estáticas y representa el estado "real" de los datos.
• DB2 es libre para que los usuarios hagan lo que deseen: ellos (o usted) tendrán que escribir código para rellenar sus tablas de formas extrañas desde DB1.

Answer 2

Como han dicho otros, no hagas esto a menos que no tengas otra opción. Un caso en el que esto es necesario es si está vendiendo un producto listo para usar que debe permitir a los usuarios registrar datos personalizados. El producto de mi empresa entra en esta categoría.

Si necesita permitir que sus clientes hagan esto, aquí hay algunos consejos:
- Cree una herramienta administrativa robusta para realizar los cambios de esquema, y ​​no permita que estos cambios se realicen de otra manera.
- Convertirlo en una función administrativa; no permita que los usuarios normales accedan a él.
- Registre cada detalle sobre cada cambio de esquema. Esto le ayudará a resolver problemas, y también le dará datos de CYA si un cliente hace algo estúpido.

Si puede hacer esas cosas con éxito (especialmente la primera), entonces funcionará cualquiera de las arquitecturas que mencionó. Mi preferencia es cambiar dinámicamente los objetos de la base de datos, porque eso le permite aprovechar las características de consulta de su DBMS cuando accede a los datos almacenados en los campos personalizados. Las otras tres opciones requieren cargar grandes cantidades de datos y luego hacer la mayor parte del procesamiento de datos en código.

Answer 3

El enfoque EAV creo que es el mejor enfoque, pero viene con un alto costo

Answer 4

El objetivo de tener una base de datos relacional es mantener sus datos seguros y consistentes. En el momento en que permite a los usuarios alterar el esquema, ahí va su integridad de datos ...

Si su necesidad es almacenar datos heterogéneos, por ejemplo, como un escenario de CMS, le sugiero que almacene XML validado por un XSD en una fila. Por supuesto, usted pierde rendimiento y capacidades de búsqueda fáciles, pero es una buena inversión en mi humilde opinión.

¡Ya es 2016, olvídate de XML! Use JSON para almacenar la bolsa de datos no relacionales, con una columna apropiadamente tipeada como back-end. Normalmente no debería necesitar consultar por valor dentro de la bolsa , lo que será lento a pesar de que muchas bases de datos SQL actuales comprenden JSON de forma nativa.

Answer 5

ElasticSearch. Debería considerarlo especialmente si está tratando con conjuntos de datos que puede dividir por fecha, puede usar JSON para sus datos y no están fijos en el uso de SQL para recuperar los datos.

ES infiere su esquema para cualquier nuevo campo JSON que envíe, de forma automática, con sugerencias, o manualmente, que puede definir / cambiar mediante un comando HTTP ("asignaciones"). Aunque no es compatible con SQL, tiene algunas excelentes capacidades de búsqueda e incluso agregaciones.

Answer 6

En el pasado elegí la opción C: crear una tabla "larga, estrecha" que almacena valores de columna dinámicos como filas que luego deben pivotar para crear un conjunto de filas "corto, ancho" que contiene todos los valores para una entidad específica. . Sin embargo, estaba usando un ORM, y eso REALMENTE hizo las cosas dolorosas. No puedo pensar en cómo lo harías en, digamos, LinqToSql. Supongo que tendré que crear una Hashtable para hacer referencia a los campos.

@Skliwz: Supongo que está más interesado en permitir que los usuarios creen campos definidos por el usuario.

Answer 7

En la wiki de c2.com, se exploró la idea de "relación dinámica". NO necesitas un DBA: las columnas y las tablas son Create-On-Write, a menos que comiences a agregar restricciones para que actúen más como un RDBMS tradicional: a medida que un proyecto madura, puedes "bloquearlo" incrementalmente.

Conceptualmente, puede pensar en cada fila como una declaración XML. Por ejemplo, un registro de empleado podría representarse como:

<employee lastname="Li" firstname="Joe" salary="120000" id="318"/>

Esto no implica que deba implementarse como XML, es solo una conceptualización práctica. Si solicita una columna que no existe, como "SELECT madeUpColumn ...", se trata como en blanco o nulo (a menos que las restricciones adicionales lo prohíban). Y es posible usar SQL , aunque hay que tener cuidado con las comparaciones debido al modelo de tipo implícito. Pero aparte del manejo de tipos, los usuarios de un sistema Dynamic Relational se sentirían como en casa porque pueden aprovechar la mayor parte de sus conocimientos de RDBMS existentes. Ahora, si alguien lo construye ...

Answer 8

Lo hice en un proyecto real:

La base de datos consistía en una tabla con un campo que era una matriz de 50. Tenía un índice de ''palabra'' establecido en ella. Todos los datos eran sin tipo, por lo que el ''índice de palabras'' funcionó como se esperaba. Los campos numéricos se representaron como caracteres y la clasificación real se realizó en el lado del cliente. (Todavía es posible tener varios campos de matriz para cada tipo de datos si es necesario).

El esquema de datos lógicos para las tablas lógicas se mantuvo dentro de la misma base de datos con diferentes filas de filas ''tipo'' (el primer elemento de la matriz). También es compatible con el control de versiones simple en el estilo de copiar y escribir usando el mismo campo ''tipo''.

Ventajas:

1. Puede reorganizar y agregar / eliminar sus columnas de forma dinámica, sin necesidad de volcar / volver a cargar la base de datos. Cualquier nuevo dato de columna puede establecerse en valor inicial (virtualmente) en tiempo cero.
2. La fragmentación es mínima, ya que todos los registros y tablas son del mismo tamaño, a veces ofrece un mejor rendimiento.
3. Todo el esquema de la tabla es virtual. Cualquier estructura de esquema lógico es posible (incluso recursiva u orientada a objetos).
4. Es bueno para los datos "escribir una vez, leer en su mayoría, no borrar / marcar como borrado" (la mayoría de las aplicaciones web en realidad son así).

Desventajas:

1. Indexación solo por palabras completas, sin abreviatura,
2. Las consultas complejas son posibles, pero con una ligera degradación del rendimiento.
3. Depende de si su sistema de base de datos preferido es compatible con matrices e índices de palabras (se implementó en PROGRESO RDBMS).
4. El modelo relacional solo está en la mente del programador (es decir, solo en el tiempo de ejecución).

Y ahora estoy pensando que el siguiente paso podría ser: implementar dicha base de datos en el nivel del sistema de archivos. Eso podría ser relativamente fácil.

Answer 9

Lo que estás proponiendo no es nuevo. Mucha gente lo ha intentado ... la mayoría ha descubierto que persiguen la flexibilidad "infinita" y en cambio terminan con mucho, mucho menos que eso. Es el "motel de cucarachas" de diseños de bases de datos: los datos entran, pero es casi imposible sacarlos. Intenta conceptualizar escribir el código para CUALQUIER tipo de restricción y verás a qué me refiero.

El resultado final generalmente es un sistema que es MUCHO más difícil de depurar, mantener y lleno de problemas de coherencia de datos. Este no es siempre el caso, pero la mayoría de las veces, así es como termina. Principalmente porque los programadores no ven venir este tren y fallan en el código defensivo en contra de él. Además, a menudo termina el caso de que la flexibilidad "infinita" realmente no es tan necesaria; es un "olor" muy malo cuando el equipo de desarrollo obtiene una especificación que dice: "Dios mío, no tengo ni idea de qué tipo de datos van a poner aquí, así que déjenme poner CUANTO" ... y los usuarios finales están bien tener tipos de atributos predefinidos que pueden usar (codificar un número de teléfono genérico, y dejarles crear un # de ellos; esto es trivial en un sistema muy normalizado y mantiene la flexibilidad y la integridad).

Si tienes un equipo de desarrollo muy bueno y eres muy consciente de los problemas que tendrás que superar con este diseño, puedes codificar con éxito un sistema bien diseñado y no demasiado peligroso. La mayor parte del tiempo

Sin embargo, ¿por qué empezar con las probabilidades tan apresuradas contra ti?

No me creas? Google "One True Lookup Table" o "single table design". Algunos buenos resultados: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056

http://thedailywtf.com/Comments/Tom_Kyte_on_The_Ultimate_Extensibility.aspx?pg=3

http://www.dbazine.com/ofinterest/oi-articles/celko22

http://thedailywtf.com/Comments/The_Inner-Platform_Effect.aspx?pg=2

Answer 10

Me parece que lo que realmente quieres es algún tipo de "meta-esquema", un esquema de base de datos que sea capaz de describir un esquema flexible para almacenar los datos reales. Los cambios de esquema dinámico son delicados y no es algo con lo que te quieras meter, especialmente si los usuarios no pueden realizar el cambio.

No va a encontrar una base de datos que se adapte mejor a esta tarea que cualquier otra, por lo que su mejor opción es simplemente seleccionar una basada en otros criterios. Por ejemplo, ¿qué plataforma estás usando para alojar el DB? ¿En qué idioma está escrita la aplicación? etc

Para aclarar lo que quiero decir con "meta-esquema":

CREATE TABLE data (
    id INTEGER NOT NULL AUTO_INCREMENT,
    key VARCHAR(255),
    data TEXT,

    PRIMARY KEY (id)
);

Este es un ejemplo muy simple, es probable que tenga algo más específico para sus necesidades (y con un poco de suerte un poco más fácil trabajar con él), pero sirve para ilustrar mi punto. Debe considerar que el esquema de la base de datos en sí mismo es inmutable a nivel de la aplicación; cualquier cambio estructural debe reflejarse en los datos (es decir, la instanciación de ese esquema).

Answer 11

Sé que es un tema viejo, pero creo que nunca pierde actualidad. Estoy desarrollando algo así en este momento. Aquí está mi enfoque. Utilizo una configuración de servidor con MySQL, Apache, PHP y Zend Framework 2 como marco de aplicación, pero debería funcionar también con otras configuraciones.

Aquí hay una guía de implementación simple, puede evolucionar usted mismo más lejos de esto.

Debería implementar su propio intérprete de lenguaje de consulta, porque el SQL efectivo sería demasiado complicado.

Ejemplo:

select id, password from user where email_address = "[email protected]"

El diseño de la base de datos física:

Tabla ''especificaciones'': (debe estar en caché en su capa de acceso a datos)

• Yo dint
• parent_id: int
• nombre: varchar (255)

Tabla ''artículos'':

• Yo dint
• parent_id: int
• spec_id: int
• datos: varchar (20000)

Contenido de la tabla ''especificaciones'':

• 1, 0, ''usuario''
• 2, 1, ''email_address''
• 3, 1, ''contraseña''

Contenido de la tabla ''elementos'':

• 1, 0, 1, ''''
• 2, 1, 2, ''[email protected]''
• 3, 1, 3, ''mi contraseña''

La traducción del ejemplo en nuestro propio lenguaje de consulta:

select id, password from user where email_address = "[email protected]"

a SQL estándar se vería así:

select 
    parent_id, -- user id
    data -- password
from 
    items 
where 
    spec_id = 3 -- make sure this is a ''password'' item
    and 
    parent_id in 
    ( -- get the ''user'' item to which this ''password'' item belongs
        select 
            id 
        from 
            items 
        where 
            spec_id = 1 -- make sure this is a ''user'' item
            and 
            id in 
            ( -- fetch all item id''s with the desired ''email_address'' child item
                select 
                    parent_id -- id of the parent item of the ''email_address'' item
                from 
                    items 
                where 
                    spec_id = 2 -- make sure this is a ''email_address'' item
                    and
                    data = "[email protected]" -- with the desired data value
            )
    )

Necesitará tener la tabla de especificaciones almacenada en caché en una matriz asociativa o hashtable o algo similar para obtener los spec_id de los nombres de las especificaciones. De lo contrario, necesitaría insertar algo más de sobrecarga de SQL para obtener el spec_id de los nombres, como en este fragmento:

¡Mal ejemplo, no use esto, evite esto, caché la tabla de especificaciones en su lugar!

select 
    parent_id, 
    data 
from 
    items 
where 
    spec_id = (select id from specs where name = "password") 
    and 
    parent_id in (
        select 
            id 
        from 
            items 
        where 
            spec_id = (select id from specs where name = "user") 
            and 
            id in (
                select 
                    parent_id 
                from 
                    items 
                where 
                    spec_id = (select id from specs where name = "email_address") 
                    and 
                    data = "[email protected]"
            )
    )

Espero que entiendas la idea y puedas determinar por ti mismo si ese enfoque es factible para ti.

¡Disfrutar! :-)

Answer 12

Sé que los modelos indicados en la pregunta se usan en todos los sistemas de producción. Una bastante grande está en uso en una gran universidad / institución de enseñanza para la que trabajo. Usan específicamente el método de tabla larga y estrecha para mapear datos recopilados por muchos sistemas de adquisición de datos variados.

Además, Google lanzó recientemente su protocolo interno de intercambio de datos, buffer de protocolo, como código abierto a través de su sitio de código. Un sistema de base de datos modelado en este enfoque sería bastante interesante.

Verifique lo siguiente:

Modelo de atributo-valor de la entidad

Buffer de Protocolo de Google

Answer 13

Tengo un requisito similar y decidí usar el MongoDB esquema.

MongoDB (de "humongous") es una base de datos de código abierto, escalable, de alto rendimiento, sin esquema y orientada a documentos escrita en el lenguaje de programación C ++. (Wikipedia)

Reflejos:

• tiene una funcionalidad de consulta rica (tal vez la más cercana a SQL DB)
• listo para producción (foursquare, sourceforge lo usa)

Lowdarks (cosas que necesitas entender, para que puedas usar mongo correctamente):

• sin transacciones (en realidad tiene transacciones pero solo en operaciones atómicas)
• esto aquí: http://ethangunderson.com/blog/two-reasons-to-not-use-mongodb/
• durabilidad ... principalmente material relacionado con ACID

Answer 14

Un campo xml fuertemente tipado en MSSQL nos ha funcionado.

Answer 15

Wikipedia tiene una excelente visión general del espacio problemático:

http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

Answer 16

sql ya proporciona una forma de cambiar su esquema: el comando ALTER.

simplemente tenga una tabla que liste los campos que los usuarios no pueden cambiar, y escriba una interfaz agradable para ALTER.