I have a .Net Windows application that continually receives data from a hardware device. Additionally, the user can change various settings and parameters from within the software. I would like to record all of this data to some form of database - both the data arriving from the h/w, and settings that the user changes. The intention is to allow the user to select a point in time and see a "snapshot" of the h/w data values and software settings present at that moment in time.
Numbers:- There will be approx 200 values to record, and it should be possible to get a snapshot at a resolution of 100ms (which is roughly the interval that data is received from the h/w). Additionally users should be able to get a snapshot from up to 60 hours in the past.
One solution I thought of was to record values only as they change (e.g. as data is received from the h/w, or when the user changes a setting). However using this approach, I can see it being quite tricky and/or slow to recreate a snapshot from the stored data. I would have to find the last recorded value for each of the 200 data items, prior to the requested snapshot time. I suspect this would also require the data tier needing knowledge of what data points constitute a snapshot (ideally the "service" should be pretty dumb, and just persist any values thrown at it, and return snapshots as requested).
I also thought of maintaining a cache of the 200 data values in the application, which I update whenever some action occurs (h/w data is received, user changes a setting). A separate timer could then push the entire collection to the data tier every 100ms. By storing each of these as a timestamped record then that's my snapshot, making subsequent retrieval trivial. The downside is that it's persisting an awful lot more data, especially as the vast majority of these 200+ values will rarely change.
Finally, another consideration is how to "truncate" old data, i.e. deleting data that is older than 60 hours as new data comes in.
Okay so it's not "big data", and writing to a database every 100ms isn't particularly intensive, but it's not something that I've had to do before (database experience has generally been limited to CRUD data entry applications!). I would be interested in opinions on the proposed approaches, thoughts on alternative solutions, and any frameworks or databases that might be of use.
