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.
