Take the 2-minute tour ×
Personal Productivity Stack Exchange is a question and answer site for people wanting to improve their personal productivity. It's 100% free, no registration required.

UPDATE: 2012-06-01 Added Non-Ideal Solution #2.5.

The Puzzle I'm Looking to Solve
As an organization and analysis nerd with professional database experience, I frequently encounter situations in my personal life that seem like they would be most efficiently and productively organized in a structured database. Examples would be my boardgame collection, this year's apartment hunt, and my knitting projects/yarn/queue. The problem has been finding a way to get them into a structured database without the process becoming a hobby unto itself.

What's Going In: By "structured data", I mean the kind you find in tables. There are multiple records ("an apartment I've looked at") that each have the same fields ("name, monthly rent, address, utilities"). Bigger systems have multiple tables interacting with each other. No matter how many tables, there's a lot of order imposed on the records so that queries are possible.

What's Coming Out: Speaking of queries, the big advantage of structured data, and structured databases, is the analysis they allow you to do. You can ask not only simple questions like "what's the median rent of all the apartments I've looked at", but also complicated ones like "What's the median rent of all the apartments I've looked at with washer/dryer hook-ups and no pent rent that are in zip code 98105".

Non-Ideal Solution #1: Dedicated Applications
There are a lot of very use-specific structured database applications around (Calibre for ebooks, iTunes for music, etc.), but I'm interested in a general solution. This is both to save the time of finding and learning a new dedicated program every time a new situation comes up, as well as to cover those situations for which there isn't a dedicated program.

Non-Ideal Solution #2: Database Frameworks
Multiple searches on Google, AlternativeTo, and SuperUser for similar-ish applications have turned up lots of database frameworks, including Access, Base, Glom, Kexi, once:radix, and Camelot. These are the "overkill" options, all capable and then some of handling the situations. However, they all also suffer from the same flaw of requiring you to "build applications". In addition to creating tables, assigning foreign keys, etc., you also have to design forms, set up users, validate data, fight with the framework interface, discover the framework is buggy/slow/inadequate, and so forth. It's just way too much tedious overhead for such small scale uses.

Non-Ideal Solution #2.5: Programming From Scratch I had thought that this one would have covered by #2 - since if I felt a database framework was too much overhead, surely rolling my own code would be only worse - but the responses I've gotten show that I need to make it explicit. I am a professional and hobbyist programmer. I have written enterprise-level production websites that use database storage. For my personal programming projects, I use Python and PostgreSQL. So I understand exactly how much effort is involved in creating a database application. And when I sit down to work on a non-programming project, I don't want to go through that effort. I want to work on my project, not this other project that is writing an application.

Also, I loathe user interface work.

Non-Ideal Solution #3: Tagging Systems
Tag-based systems are great for organizing documents and other collections of unstructured data. (In fact, I use a personal wiki, Zim, to organize my unstructured reference materials.) All the ones I've used, however, are literally incapable of the types of analysis I'm looking for. I'm still willing to look at a tag-based system, if you think it's up to the task, but please keep in mind that I'm looking to analyze data, not organize documents.

Tag-based programs that are explicitly excluded (because I've already tried them): Evernote, TiddlyWiki, Zim

Non-Ideal Solution #4: Spreadsheet-as-Database
The spreadsheet-as-database hack is usually where I end up. For the smaller situations it's adequate, if inefficient to run queries on. However, it doesn't scale at all. As soon as you have two tables that need to interact, you're working with obscure and unintuitive functions like hlookup and vlookup. Throw in data validation settings for multiple columns, conditional formatting to provide clues as to where the connections between tables are, and locked cells to avoid accidently overwriting the obscure function call you just figured out. Lots of overhead, very brittle, and you still can't get as good of queries out of it as you could a database.

(My knitting projects Excel file got up to 8 interacting worksheets before I threw my hands up in surrender and installed LibreOffice Base.)

The Ideal Solution
This structured database does not need to be particularly scalable or optimized, but the front-end needs to be entirely - and easily - manageable by a technically literate non-programmer. (That's my awkward way of saying that I'm totally cool with command line and/or text-based applications, but not with hand-coding SQL statements.) The nearly archtypical example of what I'd like is Bento, Filemaker's consumer database product for the Apple OSes. Unfortunately, the only Apple product I own is an iPod Shuffle, which doesn't quite make the cut.

Outside of core functionality and the lack of Apple access, I'm trying to go light on requirements so as to not make the questions too niche or cut off options prematurely. My personal preference is a desktop Linux application for my private use; however, I'm willing to give serious consideration to Windows or web-based applications.

So, a happy-making answer this query could easily fall into either of two categories. The first is where you tell me that I only think I have a handful of nails because I'm used to working with a hammer, that what I really have are hex-headed bolts, and here's the socket wrench I should use. The second, of course, would be a pointer towards a Bento-like product I've missed so far.

share|improve this question
    
I was just going to say tiddlywiki ;) Could you specify if this should be: - online or offline? - private or public? - PC software or Web App? –  Ula Karzelek Mar 21 '12 at 10:24
    
Clarified requirements to address your comment. Thanks. –  Belisama Mar 21 '12 at 14:53
    
Can you be more specific about why spreadsheet-as-a-database doesn't meet your needs? You say you want it to be driven from a GUI. How elaborate does the GUI have to be? Are custom-designed forms a requirement? –  Chris Quenelle Mar 22 '12 at 5:47
    
@ChrisQuenelle Reworked question to address your comment. In particular, the GUI requirement was a mistatement. I'm cool with a CLI, I was just trying to convey that I'm looking for a user interface, not a developer interface. –  Belisama Mar 23 '12 at 22:04
    
Have you investigated NoSQL database options like MongoDB et al? I have a very limited amount of exposure to them, but the impression I get is that setting up the up-front structure before you start populating data is easier than in MySQL/SQL Server/PostgreSQL. That would, at least, make non-ideal solution #2 closer to ideal. –  asfallows May 15 '12 at 13:10
show 4 more comments

10 Answers 10

I sympathise with your (all too familiar dilemma) and therefore suggest you do two things:

1) Stop searching for the perfect solution. If this were a work-related project you'd have been told to choose the least-worst option and deal with the shortcomings. I think you're in danger of turning the process of finding a solution into a hobby!

2) Seriously consider learning a general purpose programming language (e.g. python), so you don't have to rely on one specific solution giving you all you want.

Using a programming language means:

  • You can query your data whether it's in text files, spreadsheets, databases, some wiki formats or combinations of these (e.g. tags in a db, and actual data in a file system).
  • You can programatically migrate your data between systems, or make mass changes to the data structures within a system.
  • You don't have to pick the perfect system to begin with or structure the data perfectly within it, because you will always be able to query it, and also change or migrate it if need be.
  • You don't have to rely on the chosen solution's query tools, just make sure the data is at least extractable, preferably editable, using your chosen language.
  • You can create complex queries with far more ease than you could in a GUI or using SQL, trust me it's often much easier in code.

I know it's not the answer you're looking for, but I believe that the best way is to both accept a bit more imperfection, and use the power of programming to free yourself from the constraints of having to choose from, and work around, things built by other people. You'll never look back!

share|improve this answer
    
+1 OP is looking for Awesome Web Application Development without the pieces that are needed. There is no "answer" to this. If there was we'd all be unemployed. We all want a system that just works but it easy to use, maintain, etc. The devil's in the details... –  Michael Durrant May 17 '12 at 13:04
    
@AndyHasit I'm a professional programmer and know several general purpose programming languages, including Python. For most of the use cases I listed, I would spend more time coding up a system than I would using it. (There's the new hobby I don't need.) –  Belisama May 17 '12 at 19:34
    
@MichaelDurrant Who said anything about web applications? –  Belisama May 17 '12 at 19:35
    
@Belisama Ignoring why I assumed you were a "non-programmer", you are still asking for a system where you aren't going to have to write code, yet which allows structured data you can analyse. There might be a tool out there which does that without being "overkill" as you put it. But if you were willing to accept a bit of compromise on one or more fronts I think you'd find a number of the proposed solutions would actually work for you. I'm not saying that to earn points, I just think that what will help you out more than endless searching for the optimum solution. Hell, even Access would do it. –  AndyHasIt May 18 '12 at 12:27
    
@AndyHasIt The whole point of the question is that I already have options and methods that are compromises and want to make sure I'm not missing a better option. I have used or currently use all of the listed non-ideal solutions, which is how I know they're non-ideal. If something better just doesn't exists, then it doesn't exist and I'll continue to muddle through as I have. –  Belisama May 19 '12 at 12:35
add comment

You want the closest thing to Bento, running on Windows (or Linux) ... FileMaker Pro? Can't get much closer than that :)

Seriously, FileMaker is a good and relatively easy to learn database. Easy enough for non-programmers, and complicated enough for people who do know something about databases. Agreed, it's not a full fledged Oracle DB, but that's not what your looking for either.

I worked a lot with it some 20 years ago, and honestly, I could make applications (glorified db's) that my colleagues were jealous about, because they couldn't do it with their Windows-counterparts.

Since then it's become quite a bit feature-laden, and so I guess it's gonna be a little harder to learn, but I honestly think this might be something.

Personally I can relate very much to what you're looking for. Me too, I like to store everything in databases, and for many years I did that professionally. But the longer you do it, the more time you invest in it to get it exactly how you want.

Nowadays I dump everything (personal stuff I mean) in a note-taking app a la Evernote. However, I still have the urge to make it more organised, and I am still looking to develop something organised as a real database, but flexible like a note-app. Something in the lines of the "Memento"-app on Android.

It's a very hard question you're asking ;) I'm pretty sure that if you find the right answer, you'll be filthy rich ;)

Good luck!

share|improve this answer
1  
Bento is $50. Filemaker Pro starts at $300. I would be completely willing to pay the former for what I want, but the latter is rather excessive. [Also, given that Apple now owns Filemaker, I'm not real optimistic about the lifespan of the remaining Windows products.] –  Belisama Mar 29 '12 at 15:07
add comment

I've had this question asked to me before. From my perspective, the two options that have the smallest learning curve are:

  1. Evernote - you dump anything you want in a note and use tags & notebooks to categorize and search
  2. Google Forms (a Google Docs feature). Works kinda like an Excel spreadsheet with an input form attached.
share|improve this answer
    
Evernote was explicitly excluded in the question. I missed spreadsheet-as-database as a likely answer; I've updated the question to address it. –  Belisama Mar 21 '12 at 16:58
add comment

I had the same problem and have tinkered with numerous solution during the years. What I actually found to be most general and easy to use is just a shallow filesystem where I take some care to give sensible filenames to things. Then I use Spotlight (use whatever search solution your OS provides) to easily and fast find my files. One important aspect is to not make the system of folders to complicated or to deep. It must be important to be able to fast file things away.

Then I use text files for simple list, and sometimes emacs org-mode when I need some more structure.

share|improve this answer
    
Thanks, but I'm looking to organize and analyze data, not files. Zim's got me covered, there. –  Belisama Mar 22 '12 at 20:23
    
What do you use files for if you don't have data in them? Traditionally files have been used to store data, and the file system have been used to organize the files, that is, the data. –  ma8e Sep 28 '12 at 16:23
add comment

Bento can handle relationships, but I don't think it can do what you're asking it to do. As you know, proper database design includes normalisation, bridge-entities and the correct naming of entities and attributes, and a whole lot more (at least in the case where you have a database that you want to ask more complicated questions). Right? So you're basically asking for a program which can magically do all this for you without the user knowing about these things? Please clarify if you disagree

share|improve this answer
1  
The tl;dr version of the question is basically "what's the closest thing to Bento that will run on Windows or Linux". The requirements do not include proper database design; if I get the answers I need, I'm not going to angst over whether the back end is normalized or even relational. –  Belisama Mar 27 '12 at 0:58
add comment

Why don't you use a plain database like mysql? There is a great modelling tool (Mysql Workbench) to create the tables for you.

The next step would be to fill with data. Which is not very hard.

Then you need a reporting tool. I prefer jasper reports' ireport. It has a graphical tool for building queries.

All runs fine on Linux and Windows!

Edit: I know, the question was, how to not do it with database apps, but a database app doesn't need to be overwhelming and SQL-Queries are not always needed.

share|improve this answer
    
MySQL plus iReport would fall under Non-Ideal Solution #2. –  Belisama May 14 '12 at 13:43
add comment

I've been looking to solve a similar problem. To date I'm using OneNote to categorise material, and spreadsheets to manage and maintain keywords, but I'm considering having to write a custom database application to do the analyses. However, I've recently come across two applications that might offer a solution. First is nVivo 9. It's mainly targeted at academics, but from an initial look it would seem to be a promising application. The downside is the cost, at £350 for a single PC license although you can download a 30 day trial. The alternative is Qiqqa. It's free, although you can make a "contribution" to lose the advertising. It is primarily intended to aid academic research by making it easy to store and analyse PDF documents, but it keeps its "libary" on an external web server which is not ideal if you're working on confidential data.

Hope this helps.

share|improve this answer
1  
Interesting, but really just a variation of Non-Ideal Solution #3. I'm working with structured data, not documents. –  Belisama May 15 '12 at 17:54
add comment

Given the examples (boardgame collection, this year's apartment hunt, and my knitting projects/yarn/queue) I would actually use excel, optionally with cloud storage (e.g. Dropbox) for internet availability.

You haven't indicated any need to share the data or have other input it or have it available 'over the web'. Personally I have 20 years of database experience, but when it comes to tracking my own stuff (I track weight, crosswords, jigsaws and a bunch of other daily tasks/challenges) I find excel (or open office spreadsheet) is the 'easiest' way to log and track the stuff without (as you note) building a whole database application. I have some pretty complex charts and analysis too as spreadsheets have plenty of that stuff now.
If you do want web access with this approach you can always use a cloud solution like Dropbox or UbuntuOne and store the spreadsheet document there.

share|improve this answer
    
Spreadsheet-as-Database is Non-Ideal Solution #4. Access from the web is a non-issue. –  Belisama May 15 '12 at 17:49
add comment

Maybe Emacs' Org-mode is worth considering. It is ultra-customizable and really-really awesome. (And their webpage is filled to neck with hacks and manuals.)

There is also an Org-ripoff for Vim, the other legendary editor: check Vim Organizer.

Entering the Emacs/Vim world is, of course, a thing of its own :-) Being a journalist (= somewhat geeky and comfortable with the command line, but in general not that computer-literate), I still took the risk when facing a similar problem as you do. As a result, I've been enjoying a significant gain in personal productivity and for the first time, I do have a solid personal "infobase". Something that is built around a text editor (Vim), but due to using plain text files, it's also usable in the outer world.

Both Emacs and Vi are tools you need to learn, but I'd say in terms of comprehensive and sustainable personal information management systems, with all their addons they're still the ultimate best "central workstations" out there today. Both are, of course, cross-platform.

So I'd strongly recommend looking into Org - for the least, it'll probably be inspiring.

share|improve this answer
    
I've experimented with Org-mode previously, but set it aside due to glaring omissions in the documentation. That aside, I'm uncertain how the Emacs eco-system, even with its built-in table support, offers the query capabilities of a structured database (which would make it a sort-of hybrid of NISs #3 and #4). If it has options I just don't know about, would you please elaborate? –  Belisama May 16 '12 at 9:46
    
Hmm, in this case not, sorry. I haven't implemented this myself, but I think Org offers quite flexible possibilities for combining tagged and tabular data (e.g. displaying bits of information either way). I was thinking of this as possibly "inspiring" to you :-). –  user1714 May 18 '12 at 7:56
add comment

Since you're already a python programmer, I'd suggest looking at Pandas (if you haven't already). You can type up your data in a spreadsheet, save it as a CSV, and import the tables into DataFrames.

Pandas DataFrames allow sub-selection, fairly extensive SQL-like joining, as well as groupby/split-apply-combine and table reshaping functionality, that all make it really easy to do basic stats on your data, as well as many possible transformations of your data. Many basic stats are included as methods in pandas DataFrames, (see the .describe() method especially), and it's pretty simple to use more complex stats from scikit-learn or similar.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.