5 Replies Latest reply on Mar 31, 2015 10:51 AM by philmodjunk

    Entity-Attribute-Value Designs in FileMaker

    travispressestheredbutton

      Title

      Entity-Attribute-Value Designs in FileMaker

      Post

      I've inherited a database using EAV. There is an impact on performance in addition to the complexity of working with and around the design. I haven't found much on FileMaker's support with or against EAV. However, when I work in a EAV designed FM Database it doesn't feel like the optimal solution. 

      Has anyone run into this as well? 

       

        • 1. Re: Entity-Attribute-Value Designs in FileMaker
          philmodjunk

          Do you mean that each record stores the name of an attribute in field 1 and the value of that attribute in field 2?

          Example: Record one has Field 1 = "First Name", Field 2 = "Fred" and record two has Field 1 = "Last Name" , Field 2 = "Smith"...

          If so, while there are specialized cases where this can be the best way to go, I don't see at as the optimum design for a relational database. An "entity" should be the name of a table. An attribute should be the name of a field defined in a record of that table and the value of that attribute would be the data stored in that field. By grouping fields into a record--the part not done with my earlier example, you fail to take advantage of the opportunity to logically group attributes into a meaningful and useful set called a "record". This would seem to then render a lot of built in tools for working with your data useless or require a lot of extra design work/processing resources before you can handle such a diverse set of attributes as a logical group on a layout for reporting, analysis and data entry purposes.

          • 2. Re: Entity-Attribute-Value Designs in FileMaker
            travispressestheredbutton

            The current scenario is closer to

                                                                                                                                                                                         
            IDAttribute
            1Color
            2Size

             

                                                                                                                                                                                                                                         
            IDAttribute IDValue
            11Green
            21Red

             

            • 3. Re: Entity-Attribute-Value Designs in FileMaker
              philmodjunk

              And are those the only fields in the two tables?

              • 4. Re: Entity-Attribute-Value Designs in FileMaker
                travispressestheredbutton

                Yes, other junction tables connect the Attributes and Values to their objects. A Group object defines the Attributes and has many Products. Those Products have the Values. 

                • 5. Re: Entity-Attribute-Value Designs in FileMaker
                  philmodjunk

                  Then this is another example of what I was discussing earlier. It can be a very useful way for storing and working with some kinds of data and very inefficient/awkward for others.

                  Example: If I wanted to store a set of mailing addresses, I'd set up a table of address records with a field for the street name, one for the city, another for the state and so forth. You could set up a record for the street name, another record for the city a third for the state, but that seems horribly inefficient.

                  Example2:

                  But If was collecting data in a single table for a series of different measurements there might well be advantages to  using a table where one field stored "blood pressure systolic" in a text field and the value in a second, while a different records stored "Mass" and the subject's mass in Kg in another....