Relational databases for a research project

September 29, 2013 11:13 PM

Is FileMaker Pro right for this? If so, please help me understand the costs/benefits of setting FMP up for certain project-specific functions.

I'm an assistant to two architecture researchers and have been tasked with creating and populating a database of case study buildings and related texts. The project will go on for a couple of years, so I'm prepared to put significant effort into setting it up properly and establishing good workflows.

This is the data:

• Table 1: Case study buildings
• Table 2: People (architects, engineers, and so on associated with the case study buildings)
• Table 3: Texts (books and journal articles)
• Table 4: Project metadata (meeting notes, lists of search queries I've run, basically rich text content with links)

At the moment I'm using an Excel spreadsheet to manage the case study buildings and architects, an academic library management app called Papers to manage the texts, and a lightweight wiki for the project metadata.

Things I need to be able to do:

• Attach multiple potentially large files to records
• Relate buildings, architects, and texts with each other (many-to-many)
• Share the database with the two researchers, who will mostly view (not edit) the database, but will want to query the database with boolean searches
• Import records from an EndNote and other citation files

Things I'd like to be able to do:

• Assign tags to a set of search results
• Least important: Locate multiple buildings on a Google Map based on the content of the location field

My understanding is that FileMaker Pro 12 will do most if not all of these things. I've downloaded the trial and am learning to use it now. But I have some concerns:

Concern 1:
Papers is fantastic and I'm loathe to use anything else to manage the texts, but I see no way to associate texts in Papers with buildings or architects in some other database. By dropping Papers, I miss out on a lot of useful library-specific features—most importantly, the ability to import EndNote and other citation files, and export bibliographies in a given style. Can FMP be set up to process EndNote files and generate multiple new records with automatically populated fields? Would I need FMP 12 Advanced for this, and would I need to pick up a scripting/programming language to do this? (I know very basic PHP and Java.)

Because I have a feeling I might be kidding myself here, I'm seriously considering using FMP and Papers with some redundant overlap—with FMP storing the bare minimum of metadata needed to identify a text, while Papers has the rest of the metadata. I think I would lose an unacceptable amount of time in filling bibliographic details manually.

Concern 2:
I'm working remotely and don't want to use my computer to host the database. I have a VPS with Linode, but my understanding is that FMP databases can't be hosted live on Linux. (Is this correct?) Unless I buy FileMaker-ready hosting elsewhere, am I restricted to uploading a static version of the database using FMP Server? (The maximum number of users connected at time would be 3, and I have plenty of unused memory and bandwidth.)

Bypassing this issue was one of the reasons I found hosted services like knackhq attractive, but I couldn't find a service which does everything on my list.

In summary, should I use:

• FMP and commit to integrating everything,
• FMP for everything but the texts and maybe project metadata, or
• something else entirely?

posted by sakahane to Computers & Internet (8 answers total)

Papers and Endnote both export BibTeX records, which are in a text-based format you could copy and paste into a database record's field, or write scripts to have FMP process the text and populate a bibliography database.

Or you could write XSLT stylesheets to convert Papers and Endnote XML formats to FMP XML format. It might be easier to go with BibTex as an intermediate format.

As far as going the other direction, Papers will import BibTeX directly, but it looks like some extra work is needed to bring BibTeX back into Endnote.

You may need to learn some AppleScript to automate interaction between FMP and Papers/Endnote.
posted by Blazecock Pileon at 11:32 PM on September 29, 2013

Note also that Papers stores its data in a SQLite file (on a mac, ~/Documents/Papers2/Library.papers2/Database.papersdb); you can run SQL queries directly on this at the command line using the sqlite3 program. For example, you could dump out some of its tables to a SQL file, then load these into FileMaker. This would be an alternative to BibTeX for interchanging data, but is a fairly hacky one.
posted by James Scott-Brown at 1:23 PM on September 30, 2013

Best answer: Filemaker 12 can also use SQL tables directly as an external data source. I don't have any experience with Papers or EndNote so I really can't say anything useful regarding those aspects of your request, but we make extensive use of FileMaker as a SQL front end, and I know it can work well in that capacity.
posted by mosk at 1:34 PM on September 30, 2013

Response by poster: I've decided to do as mosk suggested (using SQL directly as a data source) which I didn't know was possible, doubling up with Papers on the citations until I learn to script in FMP. Thank you all!
posted by sakahane at 12:30 AM on October 7, 2013

Response by poster: I found Actual Technologies' ODBC drivers to be the easiest way to get FMP talking to the remote database.

These pages were helpful:
Configuring DSN
Connecting to MySQL through SSH
posted by sakahane at 4:00 PM on October 18, 2013

Response by poster: However, I should have checked on the limitations on working with ODBC data sources (bottom of page, including lack of support for character and binary large objects) much earlier, as it seems to mean this arrangement won't satisfy my brief.
posted by sakahane at 5:14 PM on October 18, 2013

Response by poster: As a final note: Perhaps there is a way around this using this plugin, but FileMaker's document on External SQL Sources explicitly states that the design goal of ESS is not to allow FileMaker to function as the front-end to an external database.
posted by sakahane at 6:56 PM on October 18, 2013

Sorry for the late reply, but I'm just checking back into this thread now.

FileMaker's document on External SQL Sources explicitly states that the design goal of ESS is not to allow FileMaker to function as the front-end to an external database.

I'm not a sales engineer for FMP and am not trying to push them as the right solution for you, but I can say that contrary to the cited document, we use FMP12 extensively as a SQL front end using FMP12's included ODBC drivers, and the software functions just fine in that regard.

We use FMP12 as the framework for a custom Lab Information Management System (LIMS). Used as a rapid development/rapid implementation environment, FMP12 is both robust and nimble. It isn't perfect for everything, but is is excellent at some things, and we've found it makes a fine front end to a robust MS SQL Server back end. We're not storing image or binary files, so I can't directly comment on that, but we have large tables of numeric, varchar, and datetime type data. Our columns are small, maybe 250 characters max? But our solution uses dozens of SQL tables and views. Most of our tables are 50k to 500k rows, but we have several larger data change log tables that are in excess of 10M-15M rows. We have no issues writing, updating, or manipulating this data in SQL via FMP12.

Again, I'm not a FMP12 sales engineer and have nothing at stake here, but feel free to memail me if you have specific questions about using FMP12 and SQL. I'll answer your questions if I can.
posted by mosk at 4:43 PM on October 22, 2013 [1 favorite]