It is general fairly well known that narrow tables are faster than wide tables when building in Filemaker. This note seeks to develop a discussion on this subject and to hopefully establish some useful pointers for those wishing to develop new more efficient and faster performing Solutions/Apps.
Wide and narrow refers to traditional SQL database speak where records are rows and fields are columns - hence narrow means few fields.
Filemaker is much better at doing a few things many times than doing many things many times and hence the fewer fields in a table the faster everything should happen - all other things being equal i.e. no use of unstored calcs where it matters etc.
In an effort to test this I thought it would be interesting to try creating an App taking this approach to the limit by using at it's core a single table containing a single data field and - after several months development work - that is what we have.
The general idea of SingleTableArchitecture is this:
1. Users only access records through a filtered portal of records in this single data table
2. Their access is controlled by the Keychains that they hold - the local keys for the relationship driving the portal
3. The data table has a single data field and autoupdating autoentered fields for title and summary - the title is displayed in portals - the summary is used by the portal filter
4. Each record can be of any different type, Contact, Case, Note, Task etc
5. Each record is automatically linked to an underlying table of Contacts, Cases, Notes, Tasks etc but the user has no direct access to those tables - only through the narrow data table which actually serves as a sort of secure index since the user can only ever see records to which they have been given access by obtaining the appropriate keychain.
6. There are of course some complications in beuilding such a system but the benefits become obvious when you start to test it
7. One reason why it is fast is that it is trivial to have precise control over what is being indexed and cached - you only need to permit indexing on the core RecID and Type fields and the Summary field - which will be vastly less than a traditional system where experienting with turning off indexing can be a difficult process.
8. Each record in the underlying tables is linked uniquely to the master record in the SingleTable and automatically parses data from the Single Data Field into the correct underlying data fields.
9. As you would expect there are a number of subsidiary tables providing various services like Language, Users, Resources etc and I attach a screen shot of the tables in our existing development system - but there are not many and the public version that more than 200 of you have looked at to date has over 50,000 records in the data table yet still opens and runs quickly on any device with a reasonable 3G or better connection
Benefits of the SingleTableArchitecture
a. Simplification of the user experience - since the user can make and potentially access any type of record from the same place - so we don't have to ask the user to be a virtual janitor/adminstrator and go to a particular place to do a certain thing - they can do everything from the same Ui - which offers many advantages
b. Less development work to build any given feature - for example tagging. The tagging method used works for all records of every type - there was no need to write different versions for different modules and the filter fields and controls are all just the same
c. It becomes very easy to show the user all their records and to enable them to search/filter all their records - since they are already all in a single table
d. It appears to be very fast - which is great on any device but particularly on mobile devices
e. It lends itself to building parent/child record structures making that structure potentially extensible
f. The user can just type whatever they wish into a record - if they wish to use some of that data for specific purposes they have to follow some sort of protocol to enable the system to identify it - not dissimilar from a page of html where text in a single field creates a unique web page
g. The drastic simplification of this architecture compared to the more traditional form makes this far more flexible and hence it becomes much easier to reconfigure it to meet different requirements
The Cost of SingleTableArchitecture
Initially more work than a traditional system but less work latter on once the core architecture has been established
I attach an example screen shot of the - iPhone size - App Menu and the All records screen filtered for the word "fix" - where you can see the task and the note about fulfulment are conveniently in the same place. We are now using this App for our own product management.
This is merely an outline of my vision for a SingleTableArchitecture - no doubt there will be many other approachs and some strong disagreements of the most advantagious approach - however the fundimentals are I suspect always the same - design for what FileMaker is good at and you reach a satisfactory outcome - ignore what FileMake is good at and your ability to build fast efficient Apps which can be used by Mobile devices through FMGo and FM WebDirect will be very limited - unless you restrict the App to very few records.
I would invite your comments.
October 15th, 2014