A way to synchronize data between an external device and a database?

Question

Hello,

In the application I am designing, I have to communicate with a device and store a history of data readings in a database. The device is essentially a sensor that spits out numbers via the serial port. The user end of the application is a RubyOnRails interface that allows the user to view this data and configure the device.

I am wondering what kind of connection between the database and the device you could recommend for this kind of a setup.

Up to this point, I had a custom application running on a host computer (a computer with the device connected directly through a serial port) that would serve as a bridge to a MySQL database. The application would connect directly to the MySQL database and execute queries. It works fairly well, but I am not sure if this is the best solution.

The only other alternative I see is to have an intermediate application that my custom application could connect to, instead of directly going to the database. This could be a part of the main application, or something separate. Would this be a better solution?

Would you recommend another approach?

Thank you,

Answer 1

MS SQL Server 2008 has great data synchronisation support.

SQL Server 2008 Express is free and can act as a replication subscriber (but not publisher) for clients.

Microsoft Sync Framework

Answer 2

I have a similar structure, although I fetch my data from a Web Service. The way I organize is:

1. Create classes in lib/imports, eg DailyDataImport, DailyDataSummarize (you can organize the hierarchy and names as per your wish or willingness).
2. Create a rake task under a new namespace, say import and add it to your cron job depending frequency. Take a look at Cron in Ruby. Its helpful.

This allows me to have a better control over what goes in my database.

Some questions to consider:

1. What schedule does the Device follow to populate the data?
2. Do you need the data as-is or you want a little control over it or you need to process it, like summarizing and aggregating etc.