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

[Angel Fernández]

Gracias a todos por vuestras respuestas.
En cuanto a lo que dice el maestro jachguate de montarlo en linux no puedo menos que decir ¡uff! ¡Más quisiera tener conocimientos para ello! La aplicación que me recoge los datos la voy a hacer en delphi 7 porque en la universidad que participa de este proyecto tienen una licencia. Estuve barajando otras posibilidades open source o freeware como Lazarus pero francamente y con todos mis respetos para este tipo de proyectos, lo veo aún a mucha distancia de D7. Yo tengo ya cierta experiencia en D7 (aunque como veis por mis mensajes sigo siendo un aprendiz) ¿En qué lenguaje lo tendría que hacer para linux? ¿Phyton, PHP, Java? En todos ellos partiría de cero y tendría que recorrer un gran camino para llegar hasta los conocimientos que ahora tengo en delphi.
Sin embargo, estoy totalmente de acuerdo en que en las universidades deberían utilizar y enseñar más sistemas abiertos y no corporativos (más linux y no tanto Office y Microsoft) porque al final le hacen a uno ser esclavo de pagar enormes licencias o la opción mas usada: piratear. Te pirateas Windows, Office, Autocad ... que es lo que enseñan en estas universidades (al menos lo que yo conozco).
Pero me estoy yendo de varas. Volviendo a mi aplicación y contestando un poco a delphius (gracias también por tu valiosa aportación) sí podría hacer una tabla para humedad y temperatura porque los datos que recogen estos sensores son muy parecidos (rango de 0 a 100 en humedad, de 0 a 400 en temperatura en ºK. En cambio el dato de lluvia y encharcamiento es boleano sí/no por lo que también podría ponerlos en otra tabla juntos. Una sola tabla para todo, podría ser, pero sería un poco raro. Quizá dos tablas: una para temperatura y humedad y otra para lluvia y encharcamiento. Pero entonces se me plantea una cosa. ¿Es mejor una sola tabla con cientos de millones de datos o varias tablas con decenas de millones de datos? Me refiero mejor en cuanto a velocidad.
En cuanto a por qué guardar un dato cada minuto, en realidad es lo que están haciendo ahora mismo y lo están guardando en ¡ficheros de excel! Con 34000 datos el fichero va cojo y lento de narices. Evidentemente estos datos no se pueden manejar, simplemente guardar para tener un histórico. La idea es hacer consultas por hora y día, de los que extraer conclusiones y resultados más abordables.
Me interesa mucho lo que comentas, jachguate, de backups diferenciales, porque obviamente el tema de copias de seguridad me preocupa bastante pero que espero abordar más adelante. Os preguntaré más cosas sobre la marcha si os parece bien.
Saludos para todos y gracias otra vez. Me estáis ayudando mucho.

[Casimiro Noteví]

Lo que te comenta jachguate sobre Linux, se refiere al servidor.
Haces el programa en Delphi y la base de datos, en lugar de ponerla en un Windows, lo pones en un Linux, sólo has de instalar Firebird.
La conexión es igual que en windows, ejemplo:

En Windows: 192.168.1.100:c:\datos\mibasedatos.fdb
En Linux: 192.168.1.100:/mnt/datos/mibasedatos.fdb

[Angel Fernández]

Gracias por la aclaración Casimiro. De todas formas, mi cliente será quien decida dónde colocar la base de datos y la aplicación. Pero así a ojo, dudo mucho que sepa nada de servidores ni linux. De hecho, la base de datos está pensada en principio para trabajar en local. Tú llegas con tu pendrive con los datos que has sacado del datalogger en formato CSV, lo conectas a tú pc donde tienes la base de datos en cuestión y la aplicación y ésta saca los datos y los pone en la BD de Firebird.

Sí que hemos pensado en un futuro, conectar el datalogger a un ordenador y éste a la red, para poder acceder a los datos a través de internet en vez de estar arriba y abajo con el pendrive. Pero ahora mismo nos faltan conocimientos para hacer esto. ¿Esto se hace por IP, como la cita que pones tú Casimiro? El tiempo dirá cómo lo hacemos, pero ahora mismo, una base de datos FB y una aplicación en delphi es un salto cualitativo importantísimo teniendo en cuenta que el sistema actual de guardar datos es Excel. Somos ingenieros (no informáticos ni de sistemas) y a mí me apasiona este mundo de la programación pero me quedo en aficionado. Espero que con tiempo y vuestra ayuda ir aprendiendo

Un saludo.

[[Delphius]]

Empezado por Angel Fernández

Volviendo a mi aplicación y contestando un poco a delphius (gracias también por tu valiosa aportación) sí podría hacer una tabla para humedad y temperatura porque los datos que recogen estos sensores son muy parecidos (rango de 0 a 100 en humedad, de 0 a 400 en temperatura en ºK. En cambio el dato de lluvia y encharcamiento es boleano sí/no por lo que también podría ponerlos en otra tabla juntos. Una sola tabla para todo, podría ser, pero sería un poco raro. Quizá dos tablas: una para temperatura y humedad y otra para lluvia y encharcamiento. Pero entonces se me plantea una cosa. ¿Es mejor una sola tabla con cientos de millones de datos o varias tablas con decenas de millones de datos? Me refiero mejor en cuanto a velocidad.
En cuanto a por qué guardar un dato cada minuto, en realidad es lo que están haciendo ahora mismo y lo están guardando en ¡ficheros de excel! Con 34000 datos el fichero va cojo y lento de narices. Evidentemente estos datos no se pueden manejar, simplemente guardar para tener un histórico. La idea es hacer consultas por hora y día, de los que extraer conclusiones y resultados más abordables.

Angel Fernández, por el tema Linux no puedo opinar, hay mentes entrenadas y sabias en el tema... yo quisiera exponer un poco de mi punto de vista.

Disculpa que lo diga, pero me resulta un poco de desperdicio estar ingresando información que es redundante. A lo que voy es que puedes implementar una regla del tipo:

Código Delphi [-]
If Dato.Humedad <> DatoAnterior.Humedad
   then begin 
            grabar(Dato.Humedad);
            DatoAnterior.Humedad = Dato.Humedad;
          end;

La explicación es simple: se compara el dato leído con el anterior (puede estar en una variable) y si son distintos guardarlos. El truco está entonces en que tu tabla guardará siempre y cuando se ha detectado un cambio (que no sea la hora claro está). Con esto consigues algo por el estilo:

HORA DATO
00:00 xxx
00:40 xxy

¿Que significa esto? que durante 40 min no se han detectado cambios.

Podrías diseñar tu sistema de manera que al migrar chequee las fechas y haga un análisis de los repetidos y te evitas la redundancia.
Es lo mismo tener esto:

00:00 xxx
...
00:39 xxx
00:40 xxy

Que tener esto:
00:00 xxx
00:40 xxy

¿Donde está el truco? En que se debe hacer una simple resta entre las horas para dectar los tiempos en que los datos han variado. Si se necesita saber los tiempos, dejaselo que te lo calcule, si fácilmente esa información puede ser "recuperada" a partir de otra. No se si me entiende...

La matemática es muy simple, y exacta.

Veamos con un ejemplo: supongamos que deseamos obtener el promedio de la temperatura de los primeros 6 minutos del día:
00:00 10
00:01 15
00:02 15
00:03 20
00:04 17
00:05 18
00:06 15

Promedio: (10+15+15+20+17+18+15)/6 = 18,33

Con la versión simplificada:
HORA DATO DIF_MIN
00:00 10 1 // la diferencia de min con el día anterior...
00:01 15 1
00:03 20 2 // 3-1
00:04 17 1
00:05 18 1
00:06 15 1

Promedio: (10x1+15x2+...+15x1)/6 = 18,33

Puede que te resulte complicado entenderme, pero si te fijas bien, el modelo "extendido" puede convivir con la versión "simplificada" si llevas ese campo DIF_MIN,

00:00 10 1
00:01 15 1
00:02 15 1
...
00:06 15 1

Y la matemática sigue cumpliendose... En teoría, no habría problemas en hacer convivir tus datos viejos con los nuevos (si es eso lo que te puede asustar). Por lo que las estadísticas y las operaciones que hagas no se verían afectadas por los repetidos.

Ahora tal vez tu me dirás, que deberá chequearse en la base de datos siempre el último valor... pero eso no es cierto, si no entendí mal nada impide que nos quedemos en alguna variable con los últimos datos cargados y allí mismo compararlos y hacer la carga de ser necesario. Y es en esta etapa cuando podemos hacer el cálculo de las diferencias, ya que contamos con dicha información.

Con esto te evitas tener que consultar la base de datos minuto a minuto y no haces lento al servidor al pedir que te haga esos cálculos de diferencia de minutos.

Realmente espero que se entienda mi idea.

Cuando se desee armar una consulta, se pueden hacer los ajustes para que los muestre como si siguieran trabajando con excel (si deseas), el truco aquí es trabajr gracias a ese dichoso campo DIF_MIN. Independientemente si se trata de una o 5 tablas, este esquema funciona.

Igualmente sigue haciendo falta análisis en el tema, pero creo que la idea está.

Saludos,

[Angel Fernández]

Muchas gracias Delphius por tu dedicación a mi problema. Lo que propones es... muy bueno. Eres un tío grande.
Déjame que lo piense. Me has pillado recién comido y la cabeza la tengo un poco espesita.
Un saludo.

[[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.

Yo por mi parte añadiría un campo calculado de tipo TimeStamp, que se forme a partir de la fecha y de la hora. La razón muy simple, esta sql:

Código SQL [-]
select * from temp where
  fecha_completa between '01/01/2004 23:59:59.999' and '01/01/2005 23:59:59.999'

es más fácil de entender y más legible que:

Código SQL [-]
select * from temp where
  (fecha > '01/01/2004' and hora > '23:59:59.999') and 
 (fecha < '01/01/2005' and hora  < '23:59:59.999')

sin contar con otra muchas variantes que intervengan en la búsqueda.

Por otro lado, podremos usar 2 parámetros en lugar de 4 para los sqls.

Además, siempre tendremos el campo fecha y campo hora por separado (para cuando sea necesario).

Saludos.

[[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,