7 Replies Latest reply on Jan 4, 2009 7:31 AM by Jens Teich

    Database design theory and subsequent querying

    JeffreyG

      Title

      Database design theory and subsequent querying

      Post

      First of all, I will confess that I am the farthest thing from a database expert.  Having said that I am a convert from Windows and MS Access learning Filemaker.  I have always thought that database performance was optimized with long, skinny tables as opposed to wide ones.  For example, if a detail table was tracking sales by product, my assumption was that it would be better to have a table that looked something like this:

       

      SalesEEID

      Product

      Month

      Year 

      SalesRevenue

       

      over a table that  looked like this: 

      SalesEEID

      Month

      Year

      Product1SalesRevenue

      Product2SalesRevenue 

      Product3SalesRevenue

      ProductnSalesRevenue 

       

      QUESTION #1: Does that logic hold true with Filemaker?

       

      Moving beyond the design theory, the next question is how to compare results for one period vis a vis the previous period?

      The layout would have the month column and a base year column, a base year - 1 column and a calculated % chg field.

       

      Any help is greatly appreciated!

       

      Jeff

       

       

       

        • 1. Re: Database design theory and subsequent querying
          Jens Teich
             Your first model is definitely preferable. Avoid keeping 'same sort of things' at different places. This is potential source of future complications.

          Jens
          • 2. Re: Database design theory and subsequent querying
            JeffreyG
               OK That's what I thought.  Given that structure, since I'm used to generating queries, how do create a layout to compare month by month of the current year compared to the previous year?
            • 3. Re: Database design theory and subsequent querying
              Jens Teich
                 You can grab records with dates of a specific month via a relationhip combining calculation fields with the pattern 2009-01
              year(date)&"-"& right( "0" & month(date) ; 2 )
              .

              To collect values of the month one year ago you modify this calculation
              year(date)-1 & ...
              .

              If this relatationship tends to be too slow because the number of records is too big you have to create tables and fields to store the monthly values.

              Jens


              • 4. Re: Database design theory and subsequent querying
                JeffreyG
                  

                Thanks for taking the time to explain; as I mentioned I am more familiar with queries and event-driven programming and this is brand new to me.  

                 

                 

                If you don't mind, would you please share your thoughts on using a Numeric "Month" and "Year" field vs. a Date  field?

                 

                In order to help me better understand your response, would you approach this through a portal to the productsales table or to create a special table to hold the variance data?

                 

                If the former,  I understand how to constrain the detail table through setting a global variable in the master table.  Would you create a calculated field that grabbed data from the year-1 record?

                 

                If the latter, it appears that the solution is to scan the product sale table, get a field value and then put that value into a special table designed to display the variance.  

                 

                Since I have not written a script yet, can you please assist me with the get and put functions?  Is there a good resource for learning functions and scripts? 

                 

                Jeff

                 

                • 5. Re: Database design theory and subsequent querying
                  Jens Teich
                     > If you don't mind, would you please share your thoughts
                  > on using a Numeric "Month" and "Year" field vs. a Date field?

                  The calculation fields are used as break fields. This means that they allow a grouping of dates by month:

                  date1: 2009-01-01 -> calc field: 2009-01
                  date1: 2009-01-02 -> calc field: 2009-01
                  date1: 2009-01-03 -> calc field: 2009-01
                  date1: 2009-02-01 -> calc field: 2009-02 (!)

                  > In order to help me better understand your response, would you
                  > approach this through a portal to the productsales table or to
                  > create a special table to hold the variance data?

                  I thought of creating a list with subsummaries per month.

                  Lets do it together: Lesson 1 My first list

                  1. Create a new db with name myFirstList.fp7

                  2. Create a number field called 'amount'. Create a summary field 'SumOfAmount'.

                  3. Create a date field 'date'.

                  4. Create a calc field: see my calculation from above (the first one). Close all dialogs.

                  5. Fill the records with some data, different amounts and dates.

                  6. Change to List View (View -> ... ). List looks ugly because the body part is too large vertically.

                  7. Change to Layout Mode (Cmd/Apple-L) and reduce the height of body part by dragging the bottom line.

                  8. Layouts -> Part Setup

                  9. Add a subsummary when sorted by calc field from (4). Close dialog

                  10. Move summary field (2) into the new subsummary part.

                  11. Browse mode.

                  12. Sort (records ....) by calc field (4).

                  13. Cmd/Apple - U

                  What do you see???

                  Jens






















                  • 6. Re: Database design theory and subsequent querying
                    JeffreyG
                      

                    Excellent!  It had never occurred to me to use list view.  I was hung up in form view.  Thank you.

                     

                    Now let's move on to lesson 2. Let's assume we add one field, product.  Can you nest subsummary fields and view monthly totals with a nested subsummary of product?  I tried and was unsuccessful.

                     

                    Thanks,

                    Jeff

                     

                    • 7. Re: Database design theory and subsequent querying
                      Jens Teich
                         Very good idea.

                      1. Add a field 'Product'.

                      2. Add in Layout Mode a subsummary part 'when sorted by Product'.

                      3. Place the product field in the new part.

                      4. Duplicate the summary field also into the new subsummary part.

                      5. For better debugging this choose a background color for both subsummary parts.
                      Click Label of part to activate ...

                      6. Sort by product OR by product AND month calc field

                      7. Preview Mode

                      Jens