Hello

I'm not a DBA, I'm just trying to put together something to hold my research results in postgresql. One of the things I'm trying to do is capture the configuration of my computing testbed. So I have tables like the following:

system {id, name, date_added, last_modified, etc. }

nodes {id, name, ip, date_added, last_modified, etc. }

special_hardware {id, name, date_added, last_modified, etc }

results {id, system_id, result_value, date_added}

Okay, so a system is composed of some set of nodes, so I just created a simple system_node_jn table to capture the one-to-many relationship. The trick is attaching the special hardware to the nodes (special hardware is typically expensive cards that I will stick in a node for testing, and then maybe move around to other nodes later). The problem is that when I construct a research result, I want to know the state of the entire testbed during that result. So my thought was to do a node_specialhw_jn table that included date added and last_modified fields so that I could determine the configuration at any time. But that still seems cumbersome (I don't know how to easily do sql queries that will find the relationship at that time -- though its probably possible).

I'm also a little worried about making sure the database is in a valid state without putting in place really expensive constraint processing. Some types of the system observations (i.e. some results) are collected about 2-3 times per minute, so the results tables can grow to a fairly large size -- particularly since we hope to have this data collection mechanism for the next couple years and would like to get some of our observations down to sub-second granularity (say several thousand transactions per minute continuously for the next two years with bursty periods much higher).

Here's a link to a script that will generate scripts for creating archive tables (SQL Server) and associated components.
http://dl.dropbox.com/u/2507186/Scripts/AutoArchive.sql
Using the archive tables, you can perform point-in-time queries.

Okay, showing my ignorance here, but I don't see how that is useful.

I want an online data source that I can mine. And I also want to be able to quickly determine what the state of my testbed was today, and then what the state of my testbed was during some result. Reconfigurations aren't occurring all the time, but probably a couple times a week, I'm likely to reconfigure some part of the testbed.

What am I missing?

Cheers,
Brad

The scripts create copies of each targeted production table, and triggers on the production table for copying any data modifications to the archive table.
The production table continues to hold current data.
The archive data holds all the data that was ever in the target table, with timestamps.
You can query the archive data to reconstruct what records were in the production table at any given time.