2

I have to handle data from long term (years, decades) climate monitoring stations. The data flow usually starts with raw data (voltages, etc.) plus quality check information (pressure, temperature, flow rate, etc.) generally recorded @ 1Hz. Then, the data are assigned a quality flag (human and/or program), processed (apply calibration curves) and flagged.

So, we basically end up with 2 datasets : raw and processed data. New data are typically added once a day (~500Ko/day/instrument). Simultaneous queries are not likely to ever happen.

I wanted to go for a RDBMS (we have a MySQL server) and have some experience in database design, but the IT guy keeps telling me that flat files will to the job just as well. I suspect him to try to make his life easier when it comes to backup/upgrade the MySQL.

There are not so many links between data, they don't change much, but the quality flags will change. A RDBMS is easier to compare data from different instruments on a "many days" scale, compared to daily text files.

Well, what would you advise ?

Thanks.

Improve this question
1
  • 1
    There's a couple of pieces of information that you haven't provided which might produce better answers: how structured is the data (are all the sensors producing data in a consistent format? What happens if someone swaps a sensor for a different model?) and what kind of query performance do you need. If there's a risk the data might change, and you can have slow queries, then something like flat XML or JSON files might be a better option than an RDBMS.
    – JohnL
    Commented Jun 27, 2012 at 17:44

6 Answers 6

Reset to default
5

I am facing a very similar debate in my own office: we have a large legacy system that uses flat files, and are attempting to persuade some of the long-time users to switch to a database. This is not a simple decision, and there are many, many issues that you will need to carefully consider. Here's a few:

BENEFITS of migrating to DB:

There are, of course, numerous advantages of a database system, but the ones that I think might be most relevant in your situation include:

  • Flexibile Analysis. Perhaps the raison d'être of databases -- they are capable of sophisticated, complex, relationally-oriented searches and queries. You can really slice & dice your data in far more intricate ways than with Excel.
  • Scalability. Once you move into the realm of gigabytes of data, text files really start to stretch at the seams. Databases can be much more efficient and resilient at this scale.
  • Availability & Consistency. If you have multiple users collaborating on the same data, a DB can ensure that everyone is looking at the same version and can help prevent people from overwriting each other's flags.

DRAWBACKS:

Of course, as useful as DBs are, there are a number of factors that might rule them out in your case:

  • Disruption. How many users are going to use this new system? How invested are they in the current system (how many Excel macros and script files and workflows will have to be abandoned or re-configured?) How many other applications need to interact with the data?
  • Learning Curve. A DBMS can be a real bottlenect to those who aren't familiar with them. Who is going to train the users? Will their beloved data now be virtually "locked up" behind some DBA's desk? They will have to learn to read & write SQL, and how to interact with the MySQL interface, unless you plan to create a sophisticated front-end application for them (a daunting task!)
  • Tech Support. Who is going to manage the dbms software and servers (upgrades, patches, outtages, etc)? You've already indicated that IT is reluctant. Are you prepared to manage this yourself?
  • Cost. Even though MySQL is free, there are other conversion costs to consider, particularly migration time. Is management prepared to support a several-month gap in production time?

In the end, you will have to measure for yourself whether the disruption of workflow is worth the perceived advantages. In my opinion, I would suggest that unless and until the other system-users are significantly dissatisfied with the status quo, you really need to carefully consider whether you want to spend your chips on this battle.

Improve this answer
2

Based on my understanding of your description:

  1. You have no unique key in the data

  2. All your changes occur sequentially in sequence and they affect 1 file at a time

  3. There is no relationship between records/rows

  4. No random inquiries required or random inserts/updates

  5. No security risks on who can access the data

  6. This data is not used by on-line system or shared by several systems

from the above, this is a typical case for batch file processing. If any of the points 2 through 6 are false, use a db.

One thing you can do to make your life easier is to properly structure your file names and directories so that you could, in the future, load sets of files into database automatically. Good naming conversion will help a lot.

Improve this answer
2

Do the storage requirements of these files allow you to use both? Import the flat files into a db for data analysis purposes, but maintain an archive of the flat files that you can fall back on. Ultimately I think the RDBMS solution would be more flexible for them, and allow them to gather statistics that are otherwise more tedious to derive from flat files. They may soon find that yes, there are in fact stats they are interested in now that they have the ability to query the data in a more flexible way.

Improve this answer
0

RDBMS in my opinion, is definitely a good approach. Additionally, you can also backup those flat files as BLOB in MYSQL:http://dev.mysql.com/doc/refman/5.0/en/blob.html and correlate your data with the flatfiles saved in database...so that you can have best of both. Hope this helps!

Improve this answer
0

Some of my co-workers use flat files and transfer processed data to a huge single MySQL table. Reprocessing is sometimes necessary and it is a hassle. Personally, I think it's really dirty, and they miss the beauty of RDBMS... So, I consider we are pretty much starting from scratch.

Alternatively, the IT guy proposed me to use the RDBMS to store meta data : where is what, and what is available for a given date. How about that ?

GrandmasterB : do you suggest to create a temporary database, for the lifetime of a processing run ?

kmote : your arguments about the learning curve and management consideration are interesting. I guess I can provide basic SQL queries or scripts, and who wants more will learn SQL. But I think I should anticipate and choose a reliable system ASAP. The later the change, the harder...

Another thing : RDBMS is attractive and elegant, but the initial design amount of work is quite big.

Improve this answer
-1

I made an app that might help out in some situations. It connects to a flat file and lets you run sql queries and updates on it. It might be a solution for merging the two worlds together. You can leave the data in the legacy format but still be able to analyze it with queries. Of course if your legacy data is not designed in a relational manner.... Then I guess you won't be able to do any joins. But you can pull resultsets with field expressions and order the output and drop records. And the queries and indexes are all external files from the original flat file. So they won't affect any legacy software using the flat file.
www.McCoyOnlineStore.com - type "RDBMS" into the search box. If this doesn't seem to work for you.. please let me know where it fell short for your needs... BTW be careful with any "Update" statements.. they will change the information in your flat file.

Improve this answer
1
  • -1 for not being specific on how the OP question would be solved.
    – miraculixx
    Commented Dec 29, 2012 at 16:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.