2 Replies Latest reply on May 25, 2016 2:04 PM by DanHarris

    Tables, Tables, and more Tables


      So, I'm a relatively new FM user that's designing solutions for my small business. The more I get into this, the more I've realized that I need and want to develop these databases correctly. I've been reading Database Design for Mere Mortals, going through the FM Training Series, and Lynda.com training movies.


      I now understand that the solutions I created in the past should have incorporated multiple related tables for the various subjects within the database. So, I'm now working to redesign them by the book to insure they are correct. But, the deeper I get into this the more questions I have with less answers as to why this is really necessary.


      One major concern is that I've created my single table databases with nicely formatted layouts for entering/viewing the data (one table, one nice form view layout with tab controls to show relevant fields and list view for quick viewing). I now understand that I'll need to incorporate portals for my new and various new tables to accomplish the same thing. But, these portals will now cause my layouts to look and behave more like scrollable spreadsheets with large, relatively ugly portals all over the place.


      So, I'm left wondering do I really need to have a separate table for facility addresses (physical and mailing)? Do I really need another table for facility contacts (primary, secondary, etc)? Do I really need another table for facility equipment details?  When it comes to portals, am I stuck with this spreadsheet-like formatting?


      I could really use some honest advise on all this...I have a lot to learn and want to get off on the right track. Thanks in advance.

        • 1. Re: Tables, Tables, and more Tables

          The short answer is that any time you are going to have a one to many relationship, build a table to hold the "many" items.
          You have  ONE Contact. That Contact has many touch points (emails, phones, social media etc.)  Build a table that contains contact information.


          You may also have a staff table. Some might argue that both staff and customers are both people, so use a field to identify a Contact type instead of building a separate table. Lets say you decide to use one table for staff and one table for customers.  You will only need ONE  table (Communications) to store the data for both Contacts and staff.




          You will give each Contact a unique ID. You will give each staff member a unique ID.  In the communications table, you will create a field "Staff_id" and a field "Conact_id. You will the create a relationship from your "Contacts" table to the "Communications" table and a relationship from your "Staff" table to your "Communications" table.  Use the same methodology for physical addresses.


          How you choose to isolate data in your tables will determine how may table occurrences you will have in your relationship graph.  This is well beyond the scope of your question. However, you can use different relationships between or among your tables to display different results.


          You may wish to hire a FileMaker developer to build your solution and to at the same time teach you how to build, maintain and  expand your solutions. The best question to ask any developer is WHY are you doing this. The answer better address solid business reasons for developing solutions in a particular way.


          My solution violates some data normalization rules and so programming rules. However these changes were all done for business related reasons.  The key to your successful solution is to remember, "It's the USER stupid."


          Hope this helps.

          • 2. Re: Tables, Tables, and more Tables

            Thanks for the info Hank. After some more thought I realized I wasn't looking at things correctly.


            Your post really helped put me on the right track, much appreciated.