9 Replies Latest reply on Oct 10, 2012 8:59 AM by coherentkris

    Fully Normalized Database and Presentation Layer

    andrewtraub

      I don't understand something in FileMaker.

       

      This is what I believe a fully normalized database would look like:

       

      Table

      Phones

      Field

      PhoneID (pk)

      Phone (text)

      PhoneType (number, foreign key, referenced PhoneTypeID in table PhoneType)

       

      Table

      PhoneType

      Fields

      PhoneTypeID (pk)

      Type (text)

       

      That's easy to do in FileMaker, but what I have trouble is displaying the PhoneType::Text instead of the PhoneType::PhoneTypeID after it has been selected.

       

      I've created a Value List called "Phone Type" with the first column set to PhoneTypeID and the second column set to Type and checked the "Show values only from the second field." This works well in a drop-down but once the field has been exited, the number is displayed and not the text. What am I missing?

       

      Thanks,

       

      Andrew

        • 1. Re: Fully Normalized Database and Presentation Layer
          comment

          You could use a pop-up instead of a drop-down. Alternatively, place the PhoneType::Type field onto the layout, make it non-enterable, and position it over the drop-down field.

          • 2. Re: Fully Normalized Database and Presentation Layer
            DavidJondreau

            That "show only values from second field" feature works only with a pop-up list, not a drop down menu.

            • 3. Re: Fully Normalized Database and Presentation Layer
              hrc

              If I understand you correctly, you're applying the value list to the field Phones::PhoneType on a layout of the table Phones.

              Right?

               

              So you want to store the id of PhoneType::PhoneTypeID in the field Phones::PhoneType but still show PhoneType::Type to the user.

               

              I remember that I was just as surprised as you are, when I first used this.

              What you're experiencing is actually normal FileMaker behaviour.

              Welcome to the world of FileMaker.

               

              If the value list is short, you can use a pop-up menu instead. There the behaviour is different. It's still the ID which is stored, however, the selected value is shown.

              If the value list is long, pop-up menus usually aren't practical. You then have to refrain to drop-down menus.

              What you can do to hide the ID, is

              1. to define a relationship between the tables Phones and PhoneType in the relationship graph linking Phones::PhoneType and PhoneType::PhoneTypeID together.
              2. to overlay the field Phones::PhoneType with the related field PhoneType::Type.
              3. to set the background color of the overlaying field to the same color as the background of your layout.
              4. to prevent users from Field entry into PhoneType::Type in Browse Mode
              5. to prevent users from Field entry into Phones::PhoneType in Find Mode

               

              Like that you get pretty much the behaviour you probably want.

              The only ugly detail is, that the user still sees the number while he/she's selecting something from the drop-down menu.

              • 4. Re: Fully Normalized Database and Presentation Layer
                hrc

                Sorry guys for repeating part of your statements.

                I was still typing my reply, while you were posting yours.

                • 5. Re: Fully Normalized Database and Presentation Layer
                  DavidJondreau

                  Additionally, I would question whether having a Phone Type table is actually worthwhile. Normalization can be taken too far.

                   

                  A simple value list of phone types (draw from existing values, or from a Custom, Editable list) works for me.

                  • 6. Re: Fully Normalized Database and Presentation Layer
                    hrc

                    I'd say that the table PhoneType is worthwile if users should be able to add or modify phone types over time.

                    If there are just a few immutable phone types I would go along with your suggestion to drop the table and go with a simple value list.

                    • 7. Re: Fully Normalized Database and Presentation Layer
                      andrewtraub

                      Thanks for all the suggestions!

                       

                      Andrew

                      • 8. Re: Fully Normalized Database and Presentation Layer
                        coherentkris

                        Denormalization

                        Databases intended for online transaction processing (OLTP) are typically more normalized than databases intended for online analytical processing (OLAP). OLTP applications are characterized by a high volume of small transactions such as updating a sales record at a supermarket checkout counter. The expectation is that each transaction will leave the database in a consistent state. By contrast, databases intended for OLAP operations are primarily "read mostly" databases. OLAP applications tend to extract historical data that has accumulated over a long period of time. For such databases, redundant or "denormalized" data may facilitate business intelligence applications. Specifically, dimensional tables in a star schemaoften contain denormalized data. The denormalized or redundant data must be carefully controlled during extract, transform, load (ETL) processing, and users should not be permitted to see the data until it is in a consistent state. The normalized alternative to the star schema is the snowflake schema. In many cases, the need for denormalization has waned as computers and RDBMS software have become more powerful, but since data volumes have generally increased along with hardware and software performance, OLAP databases often still use denormalized schemas.

                        Denormalization is also used to improve performance on smaller computers as in computerized cash-registers and mobile devices, since these may use the data for look-up only (e.g. price lookups). Denormalization may also be used when no RDBMS exists for a platform (such as Palm), or no changes are to be made to the data and a swift response is crucial.

                        • 9. Re: Fully Normalized Database and Presentation Layer
                          coherentkris

                          My usual thought process is normalize the data, build a logical model, then denormalize to a point where development is not hindered and the user can still get what they need easily and quickly