Post-Design Caucus Meditations

Albert and the gang at SmallCo host the FM Design Caucus on the first Thursday of the month where a group of developers get together to look at design issues using FMP. Luckily when it was my turn in November they went easy on me.

I decided to present not a recent project showing my OID model but an ongoing redesign project. While the redesign concerned only a small part of a much larger application, there were questions about the design of other parts of the application and in particular the application "data model."

I wasn't prepared to discuss the particulars of the data model chosen and how it influenced the application design although I should have been since it is what I concern myself with most. One of the questions I wasn't prepared to deflect is why split the students into two tables called "PERMANENT RECORD" and "STUDENT". The idea is a simple one - the former has all students, past, present, and future, while the latter has only the students for the current academic year - but I wasn't able to clearly describe this.

A few people questioned the split and wondered if another method might be more appropriate - pre-plugging Finds to only find current students, using row level security to restrict found sets, etc. While these methods would allow users to see only a subset of the records, they all miss the intention of the model, which is to simulate a View.

Using SQL I can create a View based on a selection from a table.

CREATE VIEW ActiveStudents AS
SELECT *
FROM Students
WHERE Status='Active'

This would allow me to consider the 440 "active" students as a set distinct from the set of 3700 students. I can then use this view to build further queries. Of course this would be using a model in which the data and the presentation of that data is separate. FileMaker does not (easily) afford us the luxury of such a model. What I advocate doing instead is explicitly creating the "view" table but instead of duplicating all of the fields found in the Students table, as my view above appears to do, only include the primary key. Relating this view table back to the source table in our RG we have access to all of the data we need.

All of this is pretty straight-forward and, I think, not very controversial. Where I think the confusion sets in is understanding two things:

1) The tasks that apply to the set of "All Students" are mostly distinct from the tasks applying to "Active Students"

2) The "ActiveStudents" table is NOT part of the data model, it is part of the presentation model

I think FileMaker induces many developers to consider the tables, fields, and relationship graph as part of their "data model." The reality is that some of the tables, fields, and relationships are part of the data model. Normalization says nothing about how to present the data - only how to efficiently organize and store that data. Because FMP doesn't give us anything, aside from our naming conventions, to help us distinguish the application model from the data model it is certain we will confuse the two. To help prevent this I use a simple rule:

Rule: FMP only concerns itself with the presentation and application model.

Where do I represent my data model? On paper of course! Sure the tables and fields and relationships will include elements of the data model. At the same time, though, there will be many more tables, fields, and relationships not included in the data model. These will be scattered among the data model elements and, aside from our naming conventions, indistinguishable from the other.

This pattern of using tables to present the user with a view of records that corresponds to the user's model has application elsewhere. It also lends itself quite nicely to the data modeling concept of Entity Classes and Entity Subclasses. Combined with the OID scripting methodology we can create a program model that is consistent with the user model yet doesn't require much in the way of additional effort. All while working within, rather than fighting against, the FileMaker idiom.

Comments

[Account Deleted]

I like the idea very much and I believe there's no need to consider the ‘active students’ table to be a part of the presentation model (as opposed to data model). There's no data model rules except normalization and the scheme fits them pretty well. It's just common to keep such simple attributes in the table, but it's also possible to consider every attribute to be an extension to an otherwise independent record.

That is if you had a Person table with names and other stuff that is common to all people and wanted to have a separate table of say, Doctors, you would probably link these two tables to reuse the fields from Person in Doctors. The sample with students is same, but it is an extreme case when the extended variant has only one different attribute.

I personally like thinking about this sample in OOP terms: the first object is Student and the second is Active Student which inherits all the properties of the Student and adds its own ‘active’ attribute.