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

[Angel Fernández]

Saludos al foro.
Quisiera ver si podéis orientarme según vuestra experiencia.
Tengo que crear una base de datos para almacenar muchísimos datos.
Se trata de recoger los datos que toman sensores de humedad, temperatura, lluvia y encharcamiento.
Estos datos se toman cada minuto a lo largo de las 24 del día todos los días del año y hay 30 sensores de humedad, otros tantos de lluvia y 2 ó 3 de lluvia y otro par de encharcamiento.
Total unos 70 sensores. Los datos los recoge un datalogger en formato csv (separados por puntos y comas), de ahí con una aplicación delphi los paso a una BD de Firebird.
Me he decidido por Firebird gracias a los buenos comentarios que he leído en este foro.
Ahora bien, haciendo cálculos así rápidos me salen aprox. 37 millones de entradas al año (24 horas x 60 minutos x 365 días x 70 sensores).

Estoy en la duda de hacer una tabla por sensor (sea de temperatura, humedad, etc no importa) del tipo:
Tabla: SENSORH1 (Sensor 1, tipo humedad)
Campos:
ID IDUBICACION FECHA HORA DATO
------------------------------------------------------------
1 2 14/01/2008 14:30:05 52.36
2 2 14/01/2008 14:31:00 52.46
ETC....

idubicacion enlazaría con otra tabla donde pondría datos de ubicación, etc.

Tabla2: SENSORT1 (Sensor 1 de temperatura)
Mismos campos

Y así hasta 70 tablas, una para cada sensor. Estas tablas almacenarían 1440 datos diarios (24 horas x 60 minutos).

Otra opción sería una tabla para humedad con los datos de todos los sensores de humedad, otra para temperatura, etc. con estos campos:

ID IDSENSOR FECHA HORA DATO
------------------------------------------------------------------
1 1 14/01/2008 14:30 xxx
2 2 14/01/2008 14:30 xxx
3 3 14/01/2008 14:30 xxx
etc.

idsensor enlazaría con otra tabla donde indicaría tipo de sensor, ubicación etc.

Así tendría 4 tablas (humedad, temp, lluvia y encharcamiento) con 100800 entradas diarias (24 x 60 x 70 sensores).

¿Qué opción os parece mejor para optimizar la base de datos? ¿Alguna otra alternativa? Y otra cosa: ¿Firebird puede con tantos registros? Hay que tener en cuenta que se desea crear un banco de datos de varios años (5 ó más). Para 7 años por ejemplo saldrían: (100800 x 365 x 7) = casi 256 millones de datos.

Agradeceré mucho vuestros comentarios, ya que hasta ahora he hecho muchas cosas en access, y muy pocas en firebird y con sólo algunos cientos de datos.

Utilizo Delphi 7, Firebird 2.0 y componentes MDO.

Gracias de antemano.

[[duilioisola]]

Una preunta importante que te debes hacer antes de seguir:
Se vana a hacer comparaciones/sumas/totales entre los sensores?

Si tienes todos los sensores en una tabla entones un selecto podría ser algo asi:

Código SQL [-]
Promedio de temperaturas de enero:
select avg(temperatura) from sensor 
where 
tipo = 'T' and 
fecha between '01/01/2008' and '01/31/2008 23:59:59'
/*y podrás acotar más la búsqueda si quieres*/

Si los tienes en diferentes tablas tendrás que hacer muchos select y sumarlos luego dentro de un procedimiento, por ejemplo.

Creo que lo mejor sería una sola tabla de esta manera

ID IDSENSOR IDUBICACION TIPO FECHA_HORA       DATO
 1      1          1      T  01/01/2008 00:01 xxxx
 2      2          5      H  01/01/2008 00:01 xxxx
 3      1          1      T  01/01/2008 00:02 xxxx
 4      2          5      H  01/01/2008 00:02 xxxx
...

Nota: El campo tipo timestamp en Firebird puede tener la fecha y la hora juntas.

Con respecto a los límites, creo que ni siquiera con 100 años llegarías a tocarlos.

Mira este link

Saludos

[Angel Fernández]

Gracias duiliosola por tu rápida respuesta. Justo después de colgar el mensaje estaba pensando justamente esto que tú me comentas. Si tengo que hacer consultas entre los sensores, y éstos los tengo desperdigados por 70 tablas, tendré que hacer otros tantos select y luego juntarlos. Creo que tu respuesta viene en la línea de lo que había empezado a pensar: quizá sea mejor una sola tabla como tú me comentas.
Es un buen comienzo. Será cuestión de darle alguna vuelta más pero por ahí van los tiros.
En cuanto a los límites, me tranquiliza leer que no hay problema.
Gracias de nuevo.

[rastafarey]

Yo te reciomiendo que hagas un diagrama ER y esto te dara las tablas y la estructura que necesite.

Ejemplo de diagram ER (Solo un parte)
Esto es un diagram muy granular
ya que lso datos extras pordrian ser direfente
o unos tener mas o menos datos qu otro se maneja
se crea una aespecificacion la cual se manejaria
por medios de vistas
Los campo comunes a cada sensor iria en Sensores
las otras cuatros solo tendri alos datos concenniente
a ellas.

Si no me explique bien puedes leer ER.


Los Sensores Estan en una ubicacion
los sensores pueden ser De
humedad
temperatura
lluvia
encharcamiento

/\
---------n / \ n-----------
|Senores|-----/esta\-----|Ubicacion|
--------- \ / -----------
| \ /
| \/
------
|------\ES/-----------------------
| \/------------------------|
| | | |
| | | |
| | | |
| | | |
---------- ------------- ------ ----------------
|humedad| |temperatura| |lluvia| |encharcamiento|
---------- ------------- ------ ----------------

Aqui tendrias 7 tablas
======================

Sensores
--------
Id
....


Ubicacion
---------
Id
...

(Donde esta ubicado cada sensor)
R Sensores Ubicacion
--------------------
Id
IdSensor
IdUbicacion
...

(Les agrego la se para saber qu eson sensores)
S_humedad
----------
Id
IdSensor
...

S_temperatura
----------
Id
IdSensor
...

S_lluvia
----------
Id
IdSensor
...

S_encharcamiento
----------
Id
IdSensor
...

La cantidad e tablas dependera de tu diagram y cuantas cosa quieras
manejar

[Angel Fernández]

Gracias rastafarey por tu participación. En efecto, algo así es lo que va tomando forma en mi cabeza. ¿Podrías darme alguna referencia de dónde aprender algo más del sistema ER que me comentas? Si busco en google "ER" al ser tan corta la cadena me salen demasiadas cosas.
Una cosa que es una tontería pero que me escama; al tener tantos datos la tabla de humedad, el campo id (autoincremento y clave primaria) debe ser del tipo biginteger ¿no? Si va a almacenar cientos de millones de datos, smallint se queda corto ¿Integer también serviría?

Un saludo.

[jachguate]

Empezado por Angel Fernández

¿Podrías darme alguna referencia de dónde aprender algo más del sistema ER que me comentas? Si busco en google "ER" al ser tan corta la cadena me salen demasiadas cosas.

ERM => Entity Relationship Model o Modelo Entidad Relación

Empezado por Angel Fernández

Una cosa que es una tontería pero que me escama; al tener tantos datos la tabla de humedad, el campo id (autoincremento y clave primaria) debe ser del tipo biginteger ¿no? Si va a almacenar cientos de millones de datos, smallint se queda corto ¿Integer también serviría?

Si tomamos la parte positiva del Integer, el límite teórico de la llave sería de 2,147,483,647 (dos mil ciento cuarenta y siete millones y pico de registros)

si hablamos de treinta y siete millones anuales que producirá tu aplicación, topará mas o menos en 58 años. Nunca se sabe cuánto tiempo estará corriendo un sistema, o si te aumenten el número de sensores. Yo usaría BigInt.

Con BigInt, podrías poner un sensor por kilometro cuadrado en el planeta, y aún así tendrías para una eternidad: 9,223,372,036,854,775,807 (nueve trillones doscientos veintitres mil trescientos setenta y dos billones, treinta y seis mil ochocientos cincuenta y cuatro millones setecientos setenta y cinco mil ochocientos siete registros) (quería escribir eso ) tomando en cuenta solamente los positivos del bigint.

Te aseguro que ningún sistema de archivos actual soportará eso..

Hasta luego.

[jachguate]

En vista del volumen de información, te recomiendo:

• Un servidor linux, en mi experiencia, el desempeño sobre exactamente el mismo hardware es bastante superior en linux que en windows, por no hablar de la estabilidad y la seguridad en general del sistema.
• Podes realizar procesos periódicos: nocturnos, semanales, mensuales, que procesen la información y la "mastiquen" para dejar tablas con resumenes tipo promedio, máximos, mínimos, de manera que las consultas y reportes que resumen información puedan hacerse sobre esas tablas, con lo que conseguirás una mejora MUY importante en el rendimiento de tu aplicación.
• Aprovecha los backups diferenciales que están disponibles ahora, dado el tamaño de la base de datos, eso también ayudará en el desempeño.

Justamente hace dos días alguien hablaba de bases de datos "enormes" en este hilo (en inglés). NO dudo que lo que allí se comente te sea de utilidad.

Hasta luego.