Base de datos enormes con firebird. ¿Cómo la estructuro?

[[Delphius]]

Empezado por Lepe

Delphius: te lo has currado de verdad.

Aunque personalmente no lo haría por varias razones:

- No son datos repetidos, de acuerdo, se repite la temperatura, pero es a horas distintas, en un primer análisis, se podría incluso considerar la hora y temperatura como clave primaria compuesta. Tampoco lo sería, ya que introducir datos relevantes como claves de la entidad relación pueden entorpecer la lógica del negocio. Si podría crearse un índice compuesto en la base de datos, ya que siempre accederemos a ambos campos, por lo que aceleramos las búsquedas.

- Dejando los datos como estaban inicialmente, podemos delegar en simples sqls la forma de hallar las medias (leasé funciones "avg" del SQL/Firebird, y otras muchas funciones estadísticas que ya están implementadas). Si optamos por tu uso extendido, tendremos que interpretar los datos (ponderar cada dato) antes de pasarlo a la función estadística.

- Si se necesitan listados, gráficas de barras que tanto gustan a los jefes [...], resulta muchísimo más fácil tirar de la Base de datos que tener que interpretar los datos.

- Saber la temperatura de una determinada fecha y hora, nos obligaría a interpretar los datos de nuevo (porque podría estar oculto en ese dif_min).

Firebird ya es un motor bastante eficiente y rápido, si hablásemos de paradox , quizás justificaría el uso de abreviaturas.

Lepe, por algo dije al final de mi mensaje que haría falta más análisis
Siempre tuve en cuenta lo que dices, de hecho es natural (y hasta se podría decir que obvio) que implementar mi idea sería un poco más "lenta" y complicada que la mantener una estructura más simple.

Yo ofrecí un punto de vista que como muchas cosas tiene su lado bueno y su lado malo. Como bien dices, si se elije mi opción habrá que interpretar la información para conseguir las operaciones.

Firebird es rápido y puede soportar la cantidad de información. Eso lo se. Yo me pregunto: ¿Que tipos de consultas se harían?¿Que informes se confeccionarían?¿Que tan problable es que se recurra a una consulta que intervenga a todos los datos?

A lo que voy es que si en las consultas no se emplean demasiados registros, podría optarse por mi método... No creo que "interpretar" los registros haga tan lento un equipo... sobre todo si las computadoras son da vez más veloces.

¿Cúantas veces al día se lanzarán las consultas? Una, dos... ¿100?
Yo creería que se puede compensar la "perdida" de tiempo en la interpretación de valores, con la cantidad de registros.

¿Cúantos registros se ahorrarían en un día?¿En una semana?¿En un mes?¿En un año? ¡Muchísimos!

Como dije, y vuelvo a decir... se necesita de un buen análisis.

Saludos,

[jachguate]

Confirmando lo ya mencionado por casimiro, cuándo hablé de Linux me refería específicamente al servidor de base de datos y no a la aplicación que le alimentará o que sacará información de él.

Sobre el tema iniciado por Delphius, comentar que, con lo que puedo entender de la aplicación de la que hablamos, me quedo con la opción de guardar todos y cada uno de los registros en la base de datos.

Por que razón: Los discos duros y la memoria son cada vez mas baratos, mientras que el tiempo que puede requerir en desarrollo de sistemas mantener y procesar datos no planos seguramente cada vez está mas caro..

Además, añadir esa complejidad al sistema, inevitablemente hará que sea mas difícil de mantener (mas fácil cagarla, en otras palabras) y mas lento el desarrollo de informes o nuevas características.

Para evitar desperdiciar tiempo innecesariamente en procesar tantos registros, he dado la idea al inicio de hacer procesos peroódicos que vayan resumiendo la información.

Por ejemplo, pensemos en la tabla de temperaturas, que tendrá esta estructura

temperatura
=================================
ID_Temperatura  bigint
ID_Sensor       integer
Hora            Timestamp
Temperatura     double precision

De esta podría derivarse las siguientes tablas:

temperatura_hora
=================================
ID_TemHora      bigint
ID_Sensor       integer
HoraInicio      TimeStamp
HoraFin         TimeStamp
Temp_Promedio   double precision
Temp_Min        double precision
Temp_Max        double precision
moda            double precision

temperatura_dia
=================================
ID_TemDia       bigint
ID_Sensor       integer
Fecha           Date
Temp_Promedio   double precision
Temp_Min        double precision
Temp_Max        double precision
moda            double precision

Estas se podrían ir llenando en el mismo proceso que carga la información a medida que se cuente con toda la necesaria (una hora completa) o un día completo, y obviamente pueden extenderse para agregar otras estadísticas.

Así, por ejemplo, saber la temperatura promedio de un año completo para un sensor particular, en lugar de tirar sobre una consulta que barra 525,600 registros, será solamente una que barra 365 (los que ya tienen información por día). Igualmente sencillo podría ser tener la temperatura promedio para la hora de 3 a 4PM de todo un año, pero seguiría siendo igual de sencillo escribir un query que nos devuelva la temperatura promedio para las 3:14 PM de todo el año.

Ya del análisis podrá determinarse si estas tablas son suficientes o si podrían crearse otras que vayan resumiendo información por otros criterios.

Desde mi punto de vista, este modelo nos permite tener lo mejor de ambos mundos.

Hasta luego.

[[Delphius]]

Juan Antonio, me saco el sombrero!

Me dejaste callado, sin palabras. Habló la experiencia.

Cuando leí en tu anterior mensaje sobre esto no lo había comprendido del todo. No se en que tenía la cabeza que ni siquiera se me cruzó por preguntartelo. Ahora lo entiendo.

¡Simplemente genial!

Bueno... si me quedan algunas dudas... más que nada por curioso:
El "cálculo" de las Temp_Min, Temp_Max, Tem_Promedio. las realizarías una vez finalidado el ejercicio? O directamente con algún Tigger After Insert de la tabla Temperatura?

Tu hablas de las dos opciones, pero en base a tu experiencia... ¿Cuál optarías? Se que en parte esto depende de las necesidades, del negocio, etc... ¿pero cual eligirías en esta ocasión?

Saludos,

[[Lepe]]

¿double precision jachguate? ... yo es que le tengo manía a ese tipo de dato, prefiero los numeric(10,2) por aquello de no perder precisión y poder encontrar una temperatura exacta.

Saludos

[Casimiro Noteví]

Empezado por Lepe

¿double precision jachguate? ... yo es que le tengo manía a ese tipo de dato, prefiero los numeric(10,2) por aquello de no perder precisión y poder encontrar una temperatura exacta.

Saludos

Yo siempre uso double precision

[rastafarey]

Yo uso un libro. De base de datos ahorita no estoy en la casa asi que no recuerdo el nombre. Pero puedes buscar en google digrama de entidad relacion o diñeno de diagramas ER.

Ahora te doy una idea de como se te van dando las cosas.

1._ Ver que es lo que necesitas.
2._ Diseño del sistema.
3._ Diagrama de procesos.
4._ Diagrama ER.
5._ Llevar el diagramas ER a tablas.
6._ Escojer el SGBD que mejor se ajuste a tus necesidades(firebird es perfecto).
7._ Preparar el manejo de la data ya sea usando disparadores vistas u otras
cosas que te permita hacer el manejar.
8._ Lenguaje de programacion.
9.- Echar codigo.

Una cosa que te recomiendo encarecidamente es que hagas todo le trabajo de la base de datos en la base de datos y no le dejes el trabajo al lenguaje de programacion.

Sacale el maximo provecho al manejador de base de datos no lo uses solo para saca y meter consultar datos. Usa la integridad refencial has tus respectivas validaciones en la bd no importa qu etambien debas ahcerla antes de que los datos se envien al servidor. Si sigues estas reglas no tenf¡dras grandes complicaciones.

Lo que te digo es con conocimiento de cuasa ya qe ahorita estamos haciendo un sistema que tiene 329 tablas. y aun faltas como 60 mas. Donde el diseño de los procesos nos tomo casi 1 año.

[jachguate]

Empezado por Delphius

El "cálculo" de las Temp_Min, Temp_Max, Tem_Promedio. las realizarías una vez finalidado el ejercicio? O directamente con algún Tigger After Insert de la tabla Temperatura?

Siempre depende.... para no sobrecargar con disparar un trigger tantas veces, podría ser la aplicación la que, cada vez que detecte un cambio de hora, dispare un stored procedure que valide aquellas horas que no se han procesado y realice el cálculo, o podría ser otra aplicación diseñada específicamente para realizar esta tarea. En este tipo de situaciones... firebird aún carece de maquinaria mas pesada. En Oracle, por ejemplo, podes definir una "tarea", que la propia base de datos dispara en base a un calendario... es como un timer, pero dentro de la propia BD... simplemente fantástico.

Empezado por Delphius

¿pero cual eligirías en esta ocasión?

Aún no lo se... pero si tuviera que tomar una decisión ahora mismo, con la información que tengo, lo haría en la aplicación que carga los datos por una razón: menos tiempo de desarrollo

Empezado por lepe

¿double precision jachguate? ... yo es que le tengo manía a ese tipo de dato, prefiero los numeric(10,2) por aquello de no perder precisión y poder encontrar una temperatura exacta.

Lo dije sin pensarlo mucho, tomando en cuenta que, bueno, son temperaturas... no monedas. Para encontrar temperaturas exactas, siempre queda el recurso de redondear, moldear como numeric o hacer un between entre un rango de "tolerancia" para la precisión (¡que exactitud!)

Hasta luego.

[[Lepe]]

Está claro que Casimiro se guarda algún "as" debajo de la manga.

En cuanto al error de precisión, no es que lo diga yo, sino los creadores de interbase en la documentación.

Quizás hace unos años estaba justificado el uso de double precision (porque ocupan menos bytes en la BD al almacenarlo), y puede que en casos como este, donde se guardarán muchísimos valores, haya que tenerlo en cuenta. Me pareció oportuno comentarlo, aunque no quiero "desvirtualizar" el hilo (¿verdad AzidRain?).

Sabido el detalle, podemos continuar con el tema original .

Saludos

[Angel Fernández]

Mi idea ahora mismo es la siguiente, componiendo en mi cabeza todo lo que me estáis diciendo:

Tabla Humedad y Temperatura
---------------------------
Dado que el tipo de dato que almacenan es parecido (rango 0-100 y 0-400 respectivamente) y los datos de H y T se estudian conjuntamente, los junto los dos en una sola tabla.
Campos
ID (bigint como me dice jachguate) IDPROYECTO (smallint) IDSENSOR (integer) FECHA (date) HORA (time) FECHAYHORA(timestamp según apunta Lepe) DATO (smallint)

IDPROYECTO enlazará con una tabla donde se indique descripción del proyecto y otros datos
IDSENSOR enlazará con la tabla de sensores

Tabla sensores
--------------
IDSENSOR TIPO(HUM, TEM, LLU, ENC) UBICACION

Tabla Lluvia y Encharcamiento
------------------------------
Por el mismo motivo junto lluvia y encharcamiento.
Campos:
ID IDPROYECTO IDSENSOR FECHA HORA FECHAYHORA DATO(true/false)

El tipo de consulta que quiero realizar, al menos para temperatura y humedad, es datos agrupados por hora para realizar gráficas. En código sql (más o menos, lo escribo de cabeza)

Código SQL [-]
select idsensor, extract(year from FECHAYHORA) as anyo, extract(month from FECHAYHORA) as mes,
extract(day from FECHAYHORA) as dia, extract(hour from FECHAYHORA) as hora,  count(DATO) as cont, min(DATO) as Minimo, max(DATO) as Maximo,
avg(DATO) as Media
from tabla1
group by idsensor, anyo, mes, dia, hora
order by idsensor, anyo, mes, dia, hora

En cambio lo de lluvia y encharcamiento no sé muy bien qué quiere el cliente (será objeto de un próximo encuentro) pero lo que comentaba Delphius me ha hecho pensar mucho. Aquí en Valencia (España) donde yo vivo, podemos tirarnos meses sin llover. ¿Para qué guardar un dato de lluvia cada minuto que sea false, false, false,... durante meses? Y lo mismo para encharcamiento.

Tendremos que definir qué queremos hacer con esos datos (quizá una consulta por mes sea suficiente) o tal vez sea para activar alguna alarma si hay encharcamiento. En este último caso, un dato cada 10 minutos como mucho sea suficiente.

De lo que me comentáis me surgen algunas dudas. ¿Las consultas las puedo hacer cada vez que las necesite o mejor guardarlas en tablas como me dice jachguate? ¿Si tengo un campo FECHAYHORA (timestamp), necesito también FECHA (date) y HORA (time), como dice Lepe, para hacer mejor las consultas por SQL?

Gracias a todos por vuestra dedicación.

Saludos.

[mamcx]

Muy linda la teoria, pero la practica es superior.

Lo mejor que puedes hacer es crear una BD, y llenarla con la cantidad de registros presupuestada, y ver que tal anda.

Ademas, el diseño es poco optimo para la lectura, como dicen la gente de digg "Datos normalizados son para niñitas" (http://www.niallkennedy.com/blog/uploads/flickr_php.pdf - el termino real es mas ofensivo!-).

Cuando se tiene un conjunto masivo de informacion y se quiere optimizar la lectura se debe hacer todo lo inverso a la logica normal:Se desnormalizan los datos.

Eso es lo que se hace para los sistemas OLAP. El ejemplo tipico es la fecha. En ves de ingresar un datetime se hace:

Fecha, Año, Mes, Dia, Hora, Min, Sec

Todo separado. Eso es genial porque puedes indexar de forma MUY eficiente.

Tambien en la medida de lo posible se precalcula todo. Por ejemplo, en este foro para sacar el numero de post, en vez de hacer un SELECT COUNT(.... se hace un campo contador en la table padre y se aumenta y decrementa de acuerdo a las repuestas del thread.

Para hacer bases de datos muy enormes, y se optimiza la lectura:

- Se separan las tablas
- Se separan los archivos de la BD enh diferentes discos. Para cosas realmente inmensas se separan en discos de red, pero este no es el caso
- Los archivos de indices se ponen en discos aparte de los de datos
- Se desnormalizan los datos y se precalculan las sumas y los conteos
- Se arman vistas, y si el motor lo soporta, se indexan.

Eso es a grandes rasgos. Pero como digo, lo mejor es que metas datos de prueba y mires antes de ir por este o aquel camino.