3 Replies Latest reply on May 22, 2014 10:27 AM by c0nsilience

    Table Vs. Field help needed -  Best Practice


      Hello All,


      I've got a simple question for you experienced developers. If one is building, for example, an Asset Database, is it more beneficial to place the assets in their own table (i.e., the HVAC table illustrated in the attached screenshot), whereas every asset category has it's own table or is it a better practice to keep the categories as a field in a main asset table? I'm relatively new to FileMaker, but I've usually done the latter.


      Any insight would be most appreciated!




      Screen Shot 2014-05-21 at 4.58.06 PM.png

        • 1. Re: Table Vs. Field help needed -  Best Practice

          FMP tables should be treated like any other DB structure designed using an Entity - attribute design philosophy. It depends on the Entity Relationship diagram. I recommend creating your Entity relationship diagram before creating the DB.


          For example:

          An Asset "Air conditioner” may be an Entity with HVAC is an attribute.


          Or a Building may be an Entity with “Air Conditioner” is an Attribute.


          But, most likely both the Building and the Air Conditioner are Entities of the Asset Table. Then I’d use a join table to make the Air Conditioner a part of the Building. Using a Bill-of-Material (BOM) structure.

          • 2. Re: Table Vs. Field help needed -  Best Practice

            Excellent analogy, JJ!


            The old-tried-and-true idea to which more people can relate: Cheeseburgers. Especially if you have it your way.

            There are items (entities) that combine to make whatever is a listing on the menu. #1 super-duper-burger = bun, burger, options. Of course these all need JOIN file to make the components into a #1 super-duper-burger. The "attributes" may well be the size of the burger, size of the bun, style of the bun (1/3 pound, medium, etc.) but added to the menu item (a recipe for the sandwich, if you will and perhaps the cost of the menu item with or without options).


            Making such examples from your type of data may help you see what needs to be tables, fields (and where located). Paper & pencil may be helpful to start thinking about these things.



            1 of 1 people found this helpful
            • 3. Re: Table Vs. Field help needed -  Best Practice

              Thank you ch0c0halic and Beverly for the great insight!  I absolutely love FileMaker, even though I'm a neophyte (have used it for a little under a year, FMP 12 and up), I really embrace it's frontend/backend design structure.  I learn something new every single time I use it and your descriptions and analogies of the ERD really help focus my perspective.


              Much appreciation to you both for breaking it down for a newbie!