At Pause On Error I was scheduled to lead a discussion about using the SQL API in FileMaker Pro but nixed it to make room for other presenters. Instead I talked about FileMaker MVC with Ernest and data modeling with David Graham of BitTailor. Because of the popularity of the topic, however, I thought I'd address a few of the questions I received in hallways and at the bar here.

One data modeling question that arose is how to deal with generalizations, such as storing contact data for a person. We can choose to store email addresses and phone numbers in the same table or different tables. Storing them in the same table makes it more difficult to provide distinct interfaces tailored for entering data or a specific type. Storing them in different tables means that you can't readily present them in a concise view when necessary. I've often suggested using a hybrid approach, that uses a generalized supertype table to store common information, and specialized subtype tables to store information specific to each contact type. This approach solves each problem, but does so through increased complexity.

On a FileMaker mailing list a question arose from someone taking the separate tables approach and presented an alternative way of addressing the problem.

Given that we have three tables:

• CONTACT with fields: IDContactPK, FirstName, LastName
• PHONENUMBER with fields: IDPhoneNumbersPK, IDContactFK, PhoneNumber
• EMAILADDRESS with fields: IDEmailAddressesPK, IDContactFK, EmailAddress

How can we get data back like so:

ContactID, FirstName, LastName, ContactInfo
1, John, Doe, john@example.com
1, John, Doe, jdoe@example.com
1, John, Doe, 407-555-1212

Of the many approaches available, they were specifically looking for a SQL solution. Recognizing that the data they wanted to combine was in separate tables, and in particular the ContactInfo column was derived from two different source tables, use of a UNION seemed to make the most sense. Here's the SQL that makes it possible.

SELECT CONTACT.IDContactPK, CONTACT.FirstName, CONTACT.LastName, EMAILADDRESS.EmailAddress AS ContactInfo
FROM CONTACT JOIN EMAILADDRESS ON CONTACT.IDContactPK = EMAILADDRESS.IDContactFK WHERE CONTACT.IDContactPK = '1'
UNION
SELECT CONTACT.IDContactPK, CONTACT.FirstName, CONTACT.LastName, PHONENUMBER.PhoneNumber AS ContactInfo
FROM CONTACT JOIN PHONENUMBER ON CONTACT.IDContactPK = PHONENUMBER.IDContactFK WHERE CONTACT.IDContactPK = '1'

Combined with some CSS formatting and presented in a web viewer, we solve the presentation problem when we want to combine the data from both sources.

One of FileMaker Pro's major advantages is the ability to rename elements without breaking your application logic. When you use the SQL API in FileMaker Pro you sacrifice this benefit. Fortunately with FileMaker Pro 10 we have a handy way of making our SQL code just as robust as native FMP functionality. A future post will show one method for doing so.