4 Replies Latest reply on Oct 15, 2014 3:13 PM by NickLightbody

    SingleTableArchitecture

    NickLightbody

      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.

       

      Cheers, Nick

      October 15th, 2014

      [ENDS]

        • 1. Re: SingleTableArchitecture
          DavidJondreau

          I tried the public version a couple months ago. It was definitely cool. I like when developers push the envelope, interesting things arise.

           

          Have you heard of NoSQL? This reminds me a little of that...you could extend this to a single field in a single table using custom functions to set and call key-value pairs! A single global field (if stored locally) could handle it. How about that for trim and slim?

           

          Seriously, I'm not sure of the benefits. For more complex applications, I don't see how you can beat a RDMS and FileMaker's native elements.

           

          But for a simple app like the one you developed, it's pretty good. And the best iOS apps, especially on the smaller screens, the best apps are very simple. I can see it being useful in those situations.

          • 2. Re: SingleTableArchitecture
            beverly

            DJ, I wonder how close NoSQL is to EAV? <http://en.wikipedia.org/wiki/Entity-attribute-value_model>. I can see the point of it's usage (and have see it used in some WordPress modules). However, SQL queries if desired, are far more complex to resolve.

             

            Beverly

            • 3. Re: SingleTableArchitecture
              NickLightbody

              David - thanks - I appreciate your comments

              Cheers, Nick

              • 4. Re: SingleTableArchitecture
                NickLightbody

                Beverly - thanks for the link - the idea of a sparse matrix fits well with some of my thoughts that evolved whilst working out how to make the current model work - the fact that in most organisations folk end up using fields designed for one thing for another becasue the field they want isn't available yet in any large system used to store human input most of the fields in most of the records are empty - the exception being mechanically collected data where the density is generally higher.

                 

                For me it goes back to some comments that Ray made in London and the fact that the most useful App I have these days is Apple Notes - as I can put any text I want in it and find it again easily on any device - it is really a personal datbase - only it don't store digital assets and it can't be shared - at least I don't think it can.

                 

                So the basic brief I gave myself was something in which I could store anything inc digital files, access easily on any device and share with other users when I wished to do so - so the access was the reverse of the norm - no share without a key as opposed to share everything and then try to restrict it.

                 

                In practice it then developed a parent / child aspect for normal client / project / transactional stuff, an everything aspect for general convenience, tagging and version control etc etc.

                 

                The aim is to be able to run a professional practice - like a law firm - from a phone or larger device without compromise other then saying no to too many little used, complex features.

                 

                Ambitious but not I think impossible

                 

                Cheers, Nick