usar una tablas para nomenclatura nombrar los las hora diseñar datos costumbre buena mysql database database-design relational partitioning

mysql - una - nomenclatura base de datos



Base de datos: diseño de una tabla de "eventos" (3)

Después de leer los consejos de este gran artículo de Nettuts +, se me ocurrió un esquema de tabla que separaría los datos altamente volátiles de otras tablas sometidas a lecturas pesadas y al mismo tiempo reduciría el número de tablas necesarias en el esquema de la base de datos, sin embargo No estoy seguro si esta es una buena idea ya que no sigue las reglas de normalización y me gustaría escuchar su consejo, aquí está la idea general:

Tengo cuatro tipos de usuarios modelados en una estructura de herencia de tabla de clase , en la tabla principal de "usuario" almaceno datos comunes a todos los usuarios ( id , username , password , varias flags , ...) junto con algunos campos TIMESTAMP ( date_created , date_updated , date_activated , date_lastLogin , ...).

Para citar la sugerencia n. ° 16 del artículo de Nettuts + mencionado anteriormente:

Ejemplo 2 : tiene un campo "last_login" en su tabla. Se actualiza cada vez que un usuario inicia sesión en el sitio web. Pero cada actualización en una tabla hace que la caché de consultas para esa tabla se vacíe. Puede poner ese campo en otra tabla para mantener al mínimo las actualizaciones de la tabla de usuarios.

Ahora es aún más complicado, necesito hacer un seguimiento de algunas estadísticas de usuario como

  • cuántas veces únicas se vio un perfil de usuario
  • cuántas veces únicas se hizo clic en un anuncio de un tipo específico de usuario
  • cuántas veces únicas se vio una publicación de un tipo específico de usuario
  • y así...

En mi base de datos completamente normalizada, esto suma alrededor de 8 a 10 tablas adicionales, no es mucho, pero me gustaría mantener las cosas simples si pudiera, así que se me ocurrió la siguiente tabla de " events ":

|------|----------------|----------------|---------------------|-----------| | ID | TABLE | EVENT | DATE | IP | |------|----------------|----------------|---------------------|-----------| | 1 | user | login | 2010-04-19 00:30:00 | 127.0.0.1 | |------|----------------|----------------|---------------------|-----------| | 1 | user | login | 2010-04-19 02:30:00 | 127.0.0.1 | |------|----------------|----------------|---------------------|-----------| | 2 | user | created | 2010-04-19 00:31:00 | 127.0.0.2 | |------|----------------|----------------|---------------------|-----------| | 2 | user | activated | 2010-04-19 02:34:00 | 127.0.0.2 | |------|----------------|----------------|---------------------|-----------| | 2 | user | approved | 2010-04-19 09:30:00 | 217.0.0.1 | |------|----------------|----------------|---------------------|-----------| | 2 | user | login | 2010-04-19 12:00:00 | 127.0.0.2 | |------|----------------|----------------|---------------------|-----------| | 15 | user_ads | created | 2010-04-19 12:30:00 | 127.0.0.1 | |------|----------------|----------------|---------------------|-----------| | 15 | user_ads | impressed | 2010-04-19 12:31:00 | 127.0.0.2 | |------|----------------|----------------|---------------------|-----------| | 15 | user_ads | clicked | 2010-04-19 12:31:01 | 127.0.0.2 | |------|----------------|----------------|---------------------|-----------| | 15 | user_ads | clicked | 2010-04-19 12:31:02 | 127.0.0.2 | |------|----------------|----------------|---------------------|-----------| | 15 | user_ads | clicked | 2010-04-19 12:31:03 | 127.0.0.2 | |------|----------------|----------------|---------------------|-----------| | 15 | user_ads | clicked | 2010-04-19 12:31:04 | 127.0.0.2 | |------|----------------|----------------|---------------------|-----------| | 15 | user_ads | clicked | 2010-04-19 12:31:05 | 127.0.0.2 | |------|----------------|----------------|---------------------|-----------| | 2 | user | blocked | 2010-04-20 03:19:00 | 217.0.0.1 | |------|----------------|----------------|---------------------|-----------| | 2 | user | deleted | 2010-04-20 03:20:00 | 217.0.0.1 | |------|----------------|----------------|---------------------|-----------|

Básicamente, la ID refiere al campo de la clave principal ( id ) en la tabla TABLE , creo que el resto debería ser bastante sencillo. Una cosa que me ha gustado en este diseño es que puedo hacer un seguimiento de todos los inicios de sesión de usuario en lugar de solo el último, y así generar algunas métricas interesantes con esos datos.

Debido a la naturaleza creciente de la tabla de events , también pensé en hacer algunas optimizaciones, tales como:

  • # 9 : Como solo hay un número finito de tablas y un número finito (y predeterminado) de eventos, las columnas TABLE y EVENTS podrían configurarse como ENUM s en lugar de VARCHAR para ahorrar espacio.
  • # 14 : Almacenar IP s como UNSIGNED INT INET_ATON() con INET_ATON() lugar de VARCHAR s.
  • Almacene DATE s como TIMESTAMP s en lugar de DATETIME s.
  • Use el motor ARCHIVE ( o el CSV ? ) En lugar de InnoDB / MyISAM .
    • Solo se admiten INSERT s y SELECT s, y los datos se comprimen sobre la marcha.

En general, cada evento solo consumiría 14 bytes (sin comprimir), lo que está bien para mi tráfico, supongo.

Pros:

  • Posibilidad de almacenar datos más detallados (como inicios de sesión).
  • No es necesario diseñar ( y codificar ) casi una docena de tablas adicionales (fechas y estadísticas).
  • Reduce unas pocas columnas por tabla y mantiene separados los datos volátiles.

Contras:

  • No relacional (aún no tan malo como EAV):
    • SELECT * FROM events WHERE id = 2 AND table = ''user'' ORDER BY date DESC();
  • 6 bytes de sobrecarga por evento ( ID , TABLE y EVENT ).

Estoy más inclinado a seguir este enfoque, ya que los profesionales parecen superar con creces los inconvenientes, pero todavía estoy un poco reacio ... ¿Me estoy perdiendo algo? ¿Cuáles son sus pensamientos sobre esto?

¡Gracias!

@coolgeek:

Una cosa que hago ligeramente diferente es mantener una tabla entity_type y usar su ID en la columna object_type (en su caso, la columna ''TABLE''). Debería hacer lo mismo con una tabla tipo_evento.

Para que quede claro, ¿quiere decir que debería agregar una tabla adicional que mapea qué eventos están permitidos en una tabla y usar el PK de esa tabla en la tabla de eventos en lugar de tener un par TABLE / EVENT ?

@ben:

Estas son todas las estadísticas derivadas de los datos existentes, ¿no?

Las tablas adicionales están relacionadas principalmente con estadísticas, pero los datos no existen, algunos ejemplos:

user_ad_stats user_post_stats ------------- --------------- user_ad_id (FK) user_post_id (FK) ip ip date date type (impressed, clicked)

Si elimino estas tablas, no tengo forma de hacer un seguimiento de quién, qué o cuándo, no estoy seguro de cómo las vistas pueden ayudar aquí.

Estoy de acuerdo en que debería estar separado, pero más porque son datos fundamentalmente diferentes. Lo que alguien es y lo que alguien hace son dos cosas diferentes. No creo que la volatilidad sea tan importante.

Lo he escuchado de ambas maneras y no pude encontrar nada en el manual de MySQL que indique que ninguno de los dos está bien. De todos modos, estoy de acuerdo con usted en que deben ser tablas separadas porque representan tipos de datos (con la ventaja adicional de ser más descriptivos que un enfoque habitual).

Creo que te estás perdiendo el bosque por los árboles, por así decirlo.

El predicado para su tabla sería "ID de usuario de IP IP en el momento FECHA EVENTADA en TABLA", lo que parece razonable, pero hay problemas.

Lo que quise decir con "no tan malo como EAV" es que todos los registros siguen una estructura lineal y son bastante fáciles de consultar, no existe una estructura jerárquica por lo que todas las consultas se pueden hacer con un simple SELECT .

En cuanto a tu segunda declaración, creo que me entendiste mal aquí; la dirección IP no está necesariamente asociada con el usuario. La estructura de la tabla debe decir algo como esto:

La dirección IP ( IP ) hizo algo ( EVENT ) al PK ( ID ) de la tabla ( TABLE ) en la fecha ( DATE ).

Por ejemplo, en la última fila de mi ejemplo anterior debería leer que IP 217.0.0.1 (algunos administradores), eliminó el usuario # 2 (cuyo último IP conocido es 127.0.0.2) en 2010-04-20 03:20:00 .

Todavía puede unir, por ejemplo, eventos de usuario a usuarios, pero no puede implementar una restricción de clave externa.

De hecho, esa es mi principal preocupación. Sin embargo, no estoy totalmente seguro de qué puede salir mal con este diseño que no podría ir mal con un diseño relacional tradicional. Puedo detectar algunas advertencias, pero mientras la aplicación que juega con la base de datos sepa lo que está haciendo, supongo que no debería haber ningún problema.

Otra cosa que cuenta en este argumento es que voy a almacenar muchos más eventos, y cada evento será más del doble en comparación con el diseño original, tiene mucho sentido usar el motor de almacenamiento ARCHIVE aquí, lo único es que no funciona. t admite FK s (ni UPDATE s ni DELETE s).

No puedo agregar un comentario a la respuesta de Ben, así que dos cosas ...

En primer lugar, una cosa sería usar vistas en una base de datos OLAP / DSS independiente; es bastante diferente usarlos en su base de datos de transacciones. Las personas de alto rendimiento MySQL recomiendan no usar vistas donde el rendimiento importa

La integridad de los datos WRT, estoy de acuerdo, y esa es otra ventaja de usar una estrella o un copo de nieve con ''eventos'' como la tabla central de hechos (así como el uso de múltiples tablas de eventos, como yo). Pero no puede diseñar un esquema de integridad referencial alrededor de las direcciones IP

Parece un diseño bastante razonable, así que solo quería desafiar algunas de tus suposiciones para asegurarte de que tienes razones concretas para lo que estás haciendo.

En mi base de datos completamente normalizada, esto suma alrededor de 8 a 10 tablas adicionales

Estas son todas las estadísticas derivadas de los datos existentes, ¿no? ( Actualización : está bien, no lo son, así que no les preste atención). ¿Por qué no serían simplemente vistas, o incluso vistas materializadas?

Sin embargo, puede parecer una operación lenta recopilar esas estadísticas:

  • una correcta indexación puede hacer que sea bastante rápido
  • no es una operación común, por lo que la velocidad no importa demasiado
  • la eliminación de datos redundantes podría hacer que otras operaciones comunes sean rápidas y confiables

He creado un esquema de tabla que separaría los datos altamente volátiles de otras tablas sometidas a lecturas pesadas

Supongo que estás hablando de cómo los eventos del usuario (solo para elegir una tabla), que serían bastante volátiles, están separados de los datos del usuario. Estoy de acuerdo en que debería estar separado, pero más porque son datos fundamentalmente diferentes. Lo que alguien es y lo que alguien hace son dos cosas diferentes.

No creo que la volatilidad sea tan importante. El DBMS ya debería permitirle colocar el archivo de registro y el archivo de base de datos en dispositivos separados, lo que hace lo mismo, y la contención no debería ser un problema con el bloqueo a nivel de fila.

No relacional (aún no tan malo como EAV)

Creo que te estás perdiendo el bosque por los árboles, por así decirlo.

El predicado para su tabla sería "ID de usuario de IP IP en el momento FECHA EVENTADA en TABLA", lo que parece razonable, pero hay problemas. (Actualización: Bien, entonces es como algo así.)

Todavía puede unir, por ejemplo, eventos de usuario a usuarios, pero no puede implementar una restricción de clave externa. Es por eso que EAV es generalmente problemático; si algo es exactamente EAV realmente no importa. Generalmente, hay una o dos líneas de código para implementar una restricción en su esquema, pero en su aplicación podría haber docenas de líneas de código, y si múltiples aplicaciones acceden a los mismos datos en múltiples lugares, se puede multiplicar fácilmente a miles de veces. líneas de código. Entonces, en general, si puede evitar los datos incorrectos con una restricción de clave externa, se le garantiza que ninguna aplicación hará eso.

Puede pensar que los eventos no son tan importantes, pero, como ejemplo, las impresiones de anuncios son dinero. Definitivamente quisiera detectar cualquier error relacionado con las impresiones de anuncios tan pronto como sea posible en el proceso de diseño.

Comentario adicional

Puedo detectar algunas advertencias, pero mientras la aplicación que juega con la base de datos sepa lo que está haciendo, supongo que no debería haber ningún problema.

Y con algunas advertencias puedes hacer un sistema muy exitoso. Con un sistema adecuado de restricciones, puede decir: "si una aplicación que juega con la base de datos no sabe lo que está haciendo, el DBMS marcará un error". Eso puede requerir más tiempo y dinero de los que tiene, así que algo más simple que puede tener es probablemente mejor que algo más perfecto que usted no puede. Así es la vida.

Recomiendo mucho este enfoque. Dado que presumiblemente está utilizando la misma base de datos para OLTP y OLAP, puede obtener importantes beneficios de rendimiento agregando algunas estrellas y copos de nieve.

Tengo una aplicación de redes sociales que actualmente se encuentra en 65 mesas. Mantengo una sola tabla para rastrear vistas de objetos (blog / post, foro / hilo, galería / álbum / imagen, etc.), otra para recomendaciones de objetos y una tercera tabla para resumir la actividad de inserción / actualización en una docena de otras tablas.

Una cosa que hago ligeramente diferente es mantener una tabla entity_type y usar su ID en la columna object_type (en su caso, la columna ''TABLE''). Debería hacer lo mismo con una tabla tipo_evento.

Aclaración para Alix : sí, mantiene una tabla de referencia para objetos y una tabla de referencia para eventos (estas serían sus tablas de dimensiones). Su tabla de hechos tendría los siguientes campos:

id object_id event_id event_time ip_address