Getting Acquainted with Access, FileMaker et al.
First of all, what’s a good name for these kinds of applications? I am talking about MS Access, FileMaker, 4th Dimension, Kexi and so on. They are not exactly databases (they can be, but they’re more than that), they are not exactly IDEs (they sort of are, but that term is not specific enough), and they are certainly not spreadsheets. Kexi’s website suggests “integrated data management applications”, so I’ll go with that for now, but the lack of a common term might confirm what I suspect: most users of, say, Microsoft Office, would have no clue what that “Access” icon is doing in between their word processor, spreadsheet, and that-thing-you-make-Powerpoint-slides-with (then again, what’s a good term for that — “presenter”?). I decided it was time to try.
I pulled up an old sample database I use for these kinds of things — it’s the last couple of years’ worth of Princeton University course offerings, and I have both MySQL and Postgres versions of it. The schema is rather normalized and has typical snowflake form; I included a diagram below (which, by the way, is from FileMaker). My goal was to get an idea of what work would have been involved in making a full-fledged course management system, without actually doing it. I tried Access, FileMaker, and 4th Dimension (Kexi will be tried once I get back to my Linux box in Norway).
Well, the first steps weren’t too bad. Since I already had a database, it was a matter of importing tables and specifying relationships. FileMaker was best at this; all tables could be imported in a single operation (unlike in 4D, where I had to go through a sequence of steps for each of the 16 tables), and the relationships diagram was responsive and easy to organize neatly. Moreover, FileMaker could operate directly on the MySQL database through ODBC rather than insisting on importing the actual data into its own proprietary database system. It’s possible Access can do something similar through it’s “Link Tables” feature, though I didn’t test that. I’d say this step took less than 30 minutes on each of the three applications, not counting the time I spent in FileMaker making the relationships diagram attractive the first time around.
I then wanted to see what work was involved with making forms for various kinds of records, especially those of the central fact table, “Course Offerings”. All of the three applications had wizards or other methods for automatically generating a basic form as a starting point. As seems to be a required feature of all integrated data management applications, they all also had various options for how to make your application really ugly or simply make sure it does not adhere to the standard look-and-feel of your local OS:
Oh, well. The forms the wizards gave me as starting points all had the same content: a simple listing of all primitive attributes in the table, surrogate primary key and everything, with textboxes to fill in values for them. It seems these wizards lack a basic understanding of how relationships work: if the “Course Offerings” table has a reverse foreign key relationship with “Readings”, it would seem natural that I’m provided with the option of creating a list of readings for each of the former. None of the wizards provide this option, so instead I was left to create the more hierarchical components of the forms myself. In 4D and Access this is can be done through “subforms”, which are simply forms contained within other forms to show a list of objects related to another. These can again contain subforms and so on, except no recursion is allowed (no cycles). FileMaker provides a more primitive concept, called a “portal”: you can have a single table of related objects within one form, but that table cannot itself contain portals.
Working with layouts seems pretty painful in all of the three programs, though I didn’t try it extensively. This is probably a very subjective opinion since I am obsessed with good alignment and also think this process should be a fully automated in the first place. 4D seems to have more features and widgets available here, while FileMaker seems most primitive. I made some subforms/portals and played a little around tidying the forms, but that’s about how far I got in my experimentation for now.
Eirik, nice writeup. Do users of FileMaker usually use its own internal database or is it common to hook them up over ODBC like you did? I didn’t realize that you can attach this class of apps to external databases, but I guess that is what makes them useful in a business environment.
I don’t know. I suspect people find it easier to use the built-in database up to a certain point; using an external database might make sense primarily for scalability and features such as replication, administration and backup tools etc. I’m not sure you’d use FileMaker as the development environment at this point anymore, though. I’ve heard of at least one company that switched from FileMaker to 4D for reasons of scalability — I think they met a hard limit on the number some kind of object.
One important point to make about the ability to use external databases is that it allows you to dynamically get/set the data from elsewhere, potentially including legacy apps and SQL scripts written by a DBA.
In the end it would seem more sensible, or at least more modular, if these applications didn’t have their own databases at all and instead relied on ODBC or a similar general interface. They could always be shipped with a simple database-in-a-file engine for user friendliness.