How to create a database for personal statistics?
October 25, 2008 8:13 AM Subscribe
Currently I've created several Excel files & sheets to keep track of my timelog, fuel expenses and statistics... but this set up is very messy, and I would like to keep all this data in a database. I'm wondering which would be the best option to do that.
This database (& interface, I presume) should allow me to enter data the way I want to, and perform calculations and statistics on it. It should run at least on a Mac (OS X 10.3) and it should be an opensource solution.
I'm able to write in C/C++, Perl, Bash, simple HTML, and I'm not afraid of learning any new language.
I've considered MySQL as a possible solution, but I don't know where to start to learn it, and more important if I can implement calculations on it and how (can I store formulas? Do I have to perform calculations and then store values?). I need to create an interface, right?
As I would like to have graphs and use statistical tools, I've considered also integrating it with R, but I'm far from sure on this: I'm not afraid of using such powerful tools (I'm a physics student and have a solid statistical/math and programming background), but I would like to use a tool that fits my needs.
This database (& interface, I presume) should allow me to enter data the way I want to, and perform calculations and statistics on it. It should run at least on a Mac (OS X 10.3) and it should be an opensource solution.
I'm able to write in C/C++, Perl, Bash, simple HTML, and I'm not afraid of learning any new language.
I've considered MySQL as a possible solution, but I don't know where to start to learn it, and more important if I can implement calculations on it and how (can I store formulas? Do I have to perform calculations and then store values?). I need to create an interface, right?
As I would like to have graphs and use statistical tools, I've considered also integrating it with R, but I'm far from sure on this: I'm not afraid of using such powerful tools (I'm a physics student and have a solid statistical/math and programming background), but I would like to use a tool that fits my needs.
You haven't told us enough about your needs to be able to say "go download open source package XYZ".
You haven't told enough about why a spreadsheet is "messy".
To be honest it sounds like a good spreadsheet is the right way to handle this kind of thing. A MySQL database is absolutely the wrong way (you think your spreadsheet is messy, just wait until you deal with installing, configuring, interacting, backing up MySQL).
There are database solutions out there that might take some of the spreadsheet messiness away. @flabdablet's recommendation of OO is good. There's also FileMaker and Bento and probably a few others. But you're running on 10.3 which is absolutely ancient these days. So good luck.
If you insist on doing it yourself with code, then I'd recommend a sqlite database using a simple scripting language like python or ruby.
But to be honest, let the spreadsheet do the work for you and deal with the messiness.
posted by schwa at 8:35 AM on October 25, 2008
You haven't told enough about why a spreadsheet is "messy".
To be honest it sounds like a good spreadsheet is the right way to handle this kind of thing. A MySQL database is absolutely the wrong way (you think your spreadsheet is messy, just wait until you deal with installing, configuring, interacting, backing up MySQL).
There are database solutions out there that might take some of the spreadsheet messiness away. @flabdablet's recommendation of OO is good. There's also FileMaker and Bento and probably a few others. But you're running on 10.3 which is absolutely ancient these days. So good luck.
If you insist on doing it yourself with code, then I'd recommend a sqlite database using a simple scripting language like python or ruby.
But to be honest, let the spreadsheet do the work for you and deal with the messiness.
posted by schwa at 8:35 AM on October 25, 2008
I'm not going to quite answer the question, but it sounds like you'd be interested in the Quantified Self community that Kevin Kelly is working on. They have a wiki that's pretty bare and he posts about meetups and such at http://www.quantifiedself.com/.
posted by adamwolf at 9:02 AM on October 25, 2008 [1 favorite]
posted by adamwolf at 9:02 AM on October 25, 2008 [1 favorite]
I agree, SQLite is closer to what you need than MySQL.
At different times when I needed to do this kind of tracking, I used one the following three different strategies:
-- I made an Excel Spreadsheet that was enhanced with a fair amount of Visual Basic. The spreadsheet alone would have been messy, but the addition of Visual Basic made it work ok. There are books that will show you how to use Excel as a database.
-- I wrote the data in parenthesized form in an Emacs buffer and used Emacs Lisp to process it. For this I wrote a small spreadsheet mode for Emacs. There are other spreadsheet modules for Emacs you might want to look at.
-- I entered my data in Gnucash, then I used a PLT Scheme with a library that parses Gnucash's xml data format and did further processing.
posted by gmarceau at 11:10 AM on October 25, 2008 [1 favorite]
At different times when I needed to do this kind of tracking, I used one the following three different strategies:
-- I made an Excel Spreadsheet that was enhanced with a fair amount of Visual Basic. The spreadsheet alone would have been messy, but the addition of Visual Basic made it work ok. There are books that will show you how to use Excel as a database.
-- I wrote the data in parenthesized form in an Emacs buffer and used Emacs Lisp to process it. For this I wrote a small spreadsheet mode for Emacs. There are other spreadsheet modules for Emacs you might want to look at.
-- I entered my data in Gnucash, then I used a PLT Scheme with a library that parses Gnucash's xml data format and did further processing.
posted by gmarceau at 11:10 AM on October 25, 2008 [1 favorite]
Response by poster: @schwa
My timelog is currently a table with 24 hours in 15m divisions and 6 categories:
1. Eating&Sleeping,
2. Time with my girlfriend
are the first two.
The others are taken from Covey's quadrant theory, namely
3. things that are important and urgent
4. important and not urgent
5. not important and urgent
6. not important and not urgent
I keep a sheet for each week and a sheet with yearly review. I want to keep my timelog for a long time, and each year adds 53 sheets.
In addition, I keep track every hour of my strength, creativity and alertness (on a scale 1-5)
You could argue that I can keep separate files for each year or each week, but I fear that keeping separate files would make long-term analysis much more difficult. For this very reason, I think it would be better to have all my statistics in one place, even if I can't see any connection between them right now (as the case of fuel statistics and timelog).
I would like to have a nice interface to input data and to be able to relate and rearrange data easily: that's why I oriented towards a database solution.
I've chosen to keep it opensource beacuse I want a long-term, stable solution, or at least the possibility to convert data if I find a better one down the road. To me adaptability is another key factor for a long-term solution, so opensource seems the way to go. One possible scenario for the future is to be able to access my data from the internet. Also, I had a very bad experience with Filemaker losing my data years ago, and that's why I'm looking for adaptability too.
@adamwolf
That sound very interesting! Thanks for the link!
@gmarceau
I used SES, but I've found that it does calculations with very poor precision. I like to do things on Emacs anyway, so I'll try to see if your spreadsheet fits my needs.
I also use John Wiegley's ledger and ledger.el to keep track of my finances and it works very well.
posted by volpe at 12:04 PM on October 25, 2008
My timelog is currently a table with 24 hours in 15m divisions and 6 categories:
1. Eating&Sleeping,
2. Time with my girlfriend
are the first two.
The others are taken from Covey's quadrant theory, namely
3. things that are important and urgent
4. important and not urgent
5. not important and urgent
6. not important and not urgent
I keep a sheet for each week and a sheet with yearly review. I want to keep my timelog for a long time, and each year adds 53 sheets.
In addition, I keep track every hour of my strength, creativity and alertness (on a scale 1-5)
You could argue that I can keep separate files for each year or each week, but I fear that keeping separate files would make long-term analysis much more difficult. For this very reason, I think it would be better to have all my statistics in one place, even if I can't see any connection between them right now (as the case of fuel statistics and timelog).
I would like to have a nice interface to input data and to be able to relate and rearrange data easily: that's why I oriented towards a database solution.
I've chosen to keep it opensource beacuse I want a long-term, stable solution, or at least the possibility to convert data if I find a better one down the road. To me adaptability is another key factor for a long-term solution, so opensource seems the way to go. One possible scenario for the future is to be able to access my data from the internet. Also, I had a very bad experience with Filemaker losing my data years ago, and that's why I'm looking for adaptability too.
@adamwolf
That sound very interesting! Thanks for the link!
@gmarceau
I used SES, but I've found that it does calculations with very poor precision. I like to do things on Emacs anyway, so I'll try to see if your spreadsheet fits my needs.
I also use John Wiegley's ledger and ledger.el to keep track of my finances and it works very well.
posted by volpe at 12:04 PM on October 25, 2008
One option that might not be exactly what you're looking for is an online database design system. DabbleDB looks really powerful, and it has strong graphing components, so it might be worth looking at. I've seen some screencasts that show off its features and interface, and it looks impressive. Lazybase is another. Both will let you design custom databases, forms, and reports online. Obviously accessible from anywhere.
posted by whatnotever at 1:10 PM on October 25, 2008 [1 favorite]
posted by whatnotever at 1:10 PM on October 25, 2008 [1 favorite]
Best answer: SQLite and SQLite Manager (firefox add-on).
posted by i_am_a_Jedi at 1:56 PM on October 25, 2008
posted by i_am_a_Jedi at 1:56 PM on October 25, 2008
This thread is closed to new comments.
posted by flabdablet at 8:20 AM on October 25, 2008