What database should I use?
January 28, 2007 12:27 PM   Subscribe

Help me avoid Microsoft. I need a simple database programme, is there a good cheap alternative to MS Access out there? Can anyone recommend OOo base?

I need to create a very small database, cataloguing an art collection of perhaps 500 pieces. I know next to nothing about databases, so I need to learn how to use a programme. Access is the obvious choice, but I'm bored of badly designed MS products, and I don't want to put the time in to get to know the programme, only to discover that its rubbish. Am I unfairly predjudiced, or should I learn OOo Base, or is there something else out there? Thanks
posted by Touchstone to Computers & Internet (25 answers total) 7 users marked this as a favorite
MySQL, or better yet, SQLite, and DB Visualizer.

The problem is that you "know next to nothing about databases", which means you're going to have to learn about primary and foreign keys, constraints, relationships, all sorts of fun stuff. Here's a primer.
posted by Civil_Disobedient at 12:48 PM on January 28, 2007

As far as OO base I'm pretty sure it would work for what you want to do but I wouldn't say that its superior to Access. They are about the same.

*** begin overkill suggestion ***

This is probably overkill but you might look into Ruby on Rails + MySQL. You could have, not only a database, but a database online that you could access and edit from anywhere, and for a simple database with basically use one table and no relationships (from what it sounds like) , it would be very easy to whip something up.

Ruby on Rails is actually a web development framework. To do this project would involve programming. Very simple programming, but programming nonetheless. This type of application is the sort of deal that an experienced RoR person could do in a half hour or so.

In fact... if you are interested, I would be interested in helping out as I am looking for some projects to put in my portfolio.

You can check my profile for my email address if you'd like.
posted by farmersckn at 12:51 PM on January 28, 2007

Another vote for SQLite. There's a binding for basically every language out there.
posted by PenDevil at 12:53 PM on January 28, 2007

Interestingly, I'm looking for solutions to precisely this problem only for a slightly larger data set (order of magnitude 15,000 records and lending and historical data). Are you set on building it, or would you consider a commercial solution? There is pre-built collection management software out there ranging from small to super-large and priced accordingly. If you're interested, I'd be happy to post what I've learned.
posted by The Bellman at 1:13 PM on January 28, 2007

Filemaker. It's rightly maligned for large-scale deployments, but for a simple db like you're talking about, it's brain-dead simple.
posted by mkultra at 1:15 PM on January 28, 2007

You could try out FileMaker. It's GUI oriented and won't demand you get a CS degree to do what you want.
posted by chairface at 1:18 PM on January 28, 2007

Dang it.
posted by chairface at 1:18 PM on January 28, 2007

Even in British English (and those Englishes that look to it to resolve pointless questions of spelling), when it means ‘a sequence of coded instructions that can be inserted into a mechanism (as a computer)’ the word has been spelled ‘program’ for a couple of decades or more.
posted by Aidan Kehoe at 1:19 PM on January 28, 2007

Access works perfectly well for this kind of thing, and you don't need to learn to program.
posted by matthewr at 1:25 PM on January 28, 2007

Another vote for FileMaker (disclaimer: I do FileMaker development for a living, so I am probably biased). It does sound perfectly suited for your needs, it is very easy to use, has a friendly GUI interface, and is more than powerful enough for the job you are describing. You can download a trial version from FileMaker's web site.
posted by mosk at 1:46 PM on January 28, 2007

I was just reading about DabbleDB, which looks interesting, but it looks like it's only available as a hosted application.
posted by Good Brain at 1:46 PM on January 28, 2007

DabbleDB, online spreadsheets. You'll want to back it up regularly, but Dabble is good enough to handle a 500-row database with (I'm guessing) a few tables and key constraints.
posted by migurski at 1:47 PM on January 28, 2007

Gotta start using that preview button. :\
posted by migurski at 1:48 PM on January 28, 2007

Response by poster: Thankyou all very much for your suggestions, advice, and spelling corrections. Just what I wanted, I'll check them out.

The Bellman I'd definitely consider a commercial solution, so I'd love to see your information. Do post or email me.

Farmersckn That might be fantastic. I'll email you tomorrow with some details, and we can discuss.
posted by Touchstone at 2:46 PM on January 28, 2007

Rekall works well, or at least it did last time I used it several years ago, but you have to install a database also, as it doesn't come with its own.
posted by wierdo at 2:54 PM on January 28, 2007

Before making your final decision, as suggested, give Filemaker a shot. It will save you money and do the job you described quite effortlessly.
posted by bkeene12 at 3:08 PM on January 28, 2007

Are you certain that it needs to be a real database? Maybe a spreadsheet would work, with features like autofilter and pivot tables, etc.

I've done stuff like this at work on a spreadsheet and never regretted not using a database.

I'm an Excel guy, so I can't attest to OpenOffice's spreadsheet program, but I'll wager it's lots easier to learn than a database program. I dabble in Access occasionally, and I'm the epitome of that "knows enough to be dangerous" expression.

Might be worth a shot.
posted by altcountryman at 3:23 PM on January 28, 2007

It will save you money and do the job you described quite effortlessly.

While I have several other reasons for disliking FileMaker, I'll just focus on this statement in my criticism. How is not free going to "save money" over free? Yes, there's a free trial version. But once you get sucked into FileMaker, you'll find they start nickel-and-diming you to death. And because the file system is (very) closed-format, you can't use any free utilities to convert your databases if you suddenly find you need to move to a different platform. It doesn't even support conversion to other open formats internally, so you're stuck (it will export the tables, but no relationships, keys, indexes... in other words, none of the structure, only the content).

I have read that the author will happily convert to other formats if you pay him. So clearly he has the tools, he just doesn't want you to have freedom over your data. This is vendor lock-in of the worst sort. I would highly, highly recommend you stay as far away from FileMaker as possible. It will not save you money.
posted by Civil_Disobedient at 4:47 PM on January 28, 2007

Lord knows I'm the last person who wants to defend FileMaker, but that's a very misinformed comment.

FileMaker, at the very least, can export easily to CSV, which can be read by any DBMS worth its salt. Unless you write your own routines, that's pretty much the same level of export you can expect out of ANY kind of database. Access, for example, won't magically create mySQL, or even MS SQL databases.

There are also ODBC drivers for FileMaker Server.

And I don't even understand this:

I have read that the author will happily convert to other formats if you pay him. So clearly he has the tools, he just doesn't want you to have freedom over your data.

Him who? Mr. FileMaker? You do realize that it's a real company with a sizeable development team, don't you? And the idea that they're hoarding some kind of conversion tools is just silly. Again, they're providing the same level of interoprability that everyone else provides. You may be thinking of some kind of Professional Services arm, or consulting partners, who of course would be happy to overcharge you for this kind of thing.
posted by mkultra at 5:01 PM on January 28, 2007

FileMaker, at the very least, can export easily to CSV, which can be read by any DBMS worth its salt.

As I said. And as I also said, it will not export anything resembling relational mapping. You have to rebuild the entire database first, then repopulate it table by table. If you have a small database, no big deal. If you have a large database, which might be a reason for moving from FileMaker to a different platform to begin with, that's when you discover this nice little gotcha. This is not a new development, and is probably the most asked question with regard to FileMaker.

Him who? Mr. FileMaker?

Yeah, you're probably right. :) I doubt Spec Bowers personally replies to the emails asking to retrieve their passwords any more. Mea culpa.
posted by Civil_Disobedient at 5:36 PM on January 28, 2007

It sounds like you've already gotten some good suggestions, but I have stand up for my much-maligned friend MS Access. IMO, it provides most of the functionality you'd expect from a database system in a relatively easy-to-use package. Shocking truth be told, I actually enjoy using it for the most part. (I also think soy milk tastes better than real milk, so go figure).
posted by treepour at 5:47 PM on January 28, 2007

How is not free going to "save money" over free? Yes, there's a free trial version. But once you get sucked into FileMaker, you'll find they start nickel-and-diming you to death.

There are several concerns when developing any sort of database solution. One is obviously the cost of the development environment or application software. FileMaker is a commercial product and as such is sold for a profit by FileMaker, Inc., a wholly owned division of Apple, Inc. They do not give it away. However, it is a very complete development environment, and it is very, very easy to use. The development time needed for a project as simple as the one the OP is describing may be an hour or less, and one can obviously spend more time writing scripts and making the experience better for the user...but the actual time spent from installing the software to defining tables and fields is laughably short. So the cost in terms of hours spent in development is very, very modest. Some folks like to save money up front, others would rather spend a bit more but save development time. FileMaker is a rapid development environment, ideal for small solutions, and a table with 500 records is *tiny*. To me, as a developer, FileMaker sounds ideal for this project.

As far as "nickle-and-diming" goes, I am not sure how that applies here. If you buy a copy of FileMaker, you can create as many database solutions with it as you would like. If you want to share your solution or sell it commercially, yes, you need to buy a more expensive version of their product and/or join their development community, but this was not mentioned in the original post, and even if this is necessary, FileMaker may still be the best option if it meets one's needs.

Finally, I would suggest that databases are NOT word processing documents; they are not portable. The DATA within them needs to be portable, and FileMaker supports exporting your dataset into many popular data formats, including CSV, .TAB, Excel, XML, etc. Database structures are notoriously specialized and non-exportable; in that sense, yes, it is a closed system, although since is supports ODBC and SQL queries, it can talk to most larger database systems.

Final disclaimer: Although I don't work for them currently, I did work for FileMaker for a number of years. I am familiar with them as a company, and with the individuals who make up that company. As a company, it has its faults; any company of 300 people that has been around for a while is going to have some baggage. However, the people that work there genuinely care about their product, and believe in it. And it is a good product, not perfect and not ideal in every situation, but remarkably able to do many tasks well. I can understand that there are better, less expensive and/or open source database products out there that will do everything that FileMaker can do, but I would submit that they CANNOT do it as easily or as quickly. IMO, If you want an easy to use database application environment, you need to at least look at FileMaker before you look past it to something else.
posted by mosk at 6:05 PM on January 28, 2007

Well said, mosk.
posted by Civil_Disobedient at 8:16 PM on January 28, 2007

I'm nth-ing FileMaker. I went through this exact dilemma a few months ago, when I agreed to help a family member migrate their small business off of Windows and Access and onto the Mac and something else.

I looked into DIYing it with MySQL and some web-based frontend, but if you're basically trying to replicate MS Access' most basic features, that's way, way overkill. It's a whole different set of skills from what you're probably used to doing in Access, which isn't to say that it's not worth learning, but that's a decision you want to make separately.

I played around with OO.org Base, and my thoughts (this is as of about a year ago now) was that it was getting there, but not to the point of being a serious Access competitor in terms of ease-of-use, stability, documentation, and functionality. (The lack of documentation is killer to me -- if it has features, but they aren't documented in any useful way, then it might as well not have them.)

FileMaker isn't the same as Access; it does some key things differently that you're going to have to get used to (there's no direct equivalent to Access' "Table View," which threw me for a while -- you don't ever look at the data 'directly,' it's always edited and managed through a View of some type), but it's all reasonably logical. Once you start to get it, suddenly a lot of things just make sense. Or at least, they did to me.

One book I'd suggest, that I found invaluable, was "FileMaker Pro 8: The Missing Manual." You can grab it from Amazon for twenty bucks or so; I think it's worth every penny, and I don't often buy or read instructional books on anything. Also, the folks at FMForums are great if you get stuck and need an answer.
posted by Kadin2048 at 8:57 PM on January 28, 2007

I 2nd altcountryman, that you might be able to acheive your goals using Excel, not a full-on database program. My experience has been that Access is much harder wrap your mind around than Excel; it's a huge leap.

It sounds like you would use Sheet1 (ArtData) to make the list of each art piece, and have lots of column headers regarding different aspects of each piece. If you want to choose from a list, you can set up on Sheet2 (List Ranges) the list of possible choices, then search Help for "designate valid cell entries". If you have another list, like artist names, addresses, etc., you could set that up as Sheet3 (Artists), and if you want to assign/link to them from Sheet1, try using the valid cell entries thing I mentioned to choose the artist in one column on Sheet1, then use "vlookup" to get the rest of the information from Sheet3 onto Sheet1. If you need to create a Purchase Order program, put that on Sheet4, starting with the sequential Order Numbers, and vlookup the rest of the info from Sheet1.

To create reports or actual Purchase Orders, or correspondence, just use Mail merge in Word.

I use this method every day, and it is much easier than Access.
posted by Sprout the Vulgarian at 7:27 AM on January 29, 2007

« Older Hoe does the new ESPN Shot Spot work?   |   Why do I get angry when holding my arms above my... Newer »
This thread is closed to new comments.