4 Replies Latest reply on Jul 10, 2009 9:09 AM by etripoli

    Do I need 2 tables or 3?



      Do I need 2 tables or 3?


      I am struggling with primary/foreign keys and I think the problem is I am not thinking FMP enough. 


      My project involves tracking daily statistics. Each record has a date and a few numbers. Until now this has been a simple spreadsheet.


      Each week I total up the stats for that week and calculate averages. This goes into a separate document with a weekly summary that I write-up.


      Rather than having 13 documents per quarter, and a limited ability to do long-term trend analysis, I thought this would be a good chance to learn FMP. Forgive my naiveté. 


      First I made a parent WEEKLY_REPORT table with a weekly_reportID primary key, and a child DAILY_REPORT table fk_weekly_reportID foreign key to link them. Except I couldn't figure out how all fit into the FiscalYear/Quarter/Week system. The year is divided into 4 quarters of 13 weeks. So when my boss says get me the FY09Q03W09 I want to easily pull out a report. I didn't want to create FY/Q/W data for every record in both the DAILY_REPORT and WEEKLY_REPORT, thinking that repetition of data is bad.


      So, I thought I could break these into three tables:  DAILY_REPORT which is linked to WEEKLY_REPORT and both are joined by a CALENDAR table that is basically a list of the quarters and weeks and their start and end dates. Then I could make reports based on Saved Searches that simply pull out the relevant records for a selected Quarter and Week (ie. a search of records with a DATE that falls between the start and end dates on the CALENDAR table).


      Unfortunately this is not going as well as planned. Am I making a simple problem unnecessarily complex? Should I just give up and put the quarter and week data into my daily reports?

        • 1. Re: Do I need 2 tables or 3?
             I dont see why you need 3 tables at all... All this can be done with one table from what you have stated. All you are doing in reporting. You dont have to create different table to report the same data. All you have to do is find the set of records you want in a range and then report on that found set. IF you do not want to type in a date range when finding records such as 1/1/2009 ... 3/31/2009 ( 1st Qtr ), then you can also lookin into creating calc using the function WeekOfYearFiscal or WeekofYear along with the Year () function.
          • 2. Re: Do I need 2 tables or 3?

            Thanks for the quick reply mr_vodka. 


            While I was in the shower tonight it dawned on me. I realized that since my reports are all based on weeks, I could just make each record contain that week's stats. No need for a DAILY or CALENDAR table at all. No relations whatsoever. Maybe I was over-worried about normalization. I will try this at work tomorrow. Though it sounds like an extremely complicated table. One record will have to contain 12 numbers for each of the 7 days of the week, plus totals and averages, plus a summary field and also all the FY-Q-W data.


            If anybody thinks I am going overboard in consolidating ALL my data on one record, please warn me now.



            • 3. Re: Do I need 2 tables or 3?

              You are being warned. By putting all of a week's data in a single record you are making it impossible to produce a report by month, quarter or year (none of these respect the weekly cycle) or by any other arbitrary period. You won't be able even to find records of January 2009 or records between Jan 15 and Feb 15, 2009, for example.


              I don't see why you need to keep records of your reports - they are easily reproducible from the original data. As mr_vodka said, all of your reporting can be done from the same data table. All you need is to add a few simple calculation fields to serve as the breakfields for week, month, quarter, year, etc.




              • 4. Re: Do I need 2 tables or 3?
                   If you are actually 'printing' reports, consider the subsummary feature when editing the layout.  You can use it to group your daily records into weekly, monthly, quarterly, etc, by creating appropriate calculation fields that determine the week, month, quarter, year, etc of the individual records.  If you are just viewing the data, create static tables, one for weeks (and create 52 records, with ascending week number fields), one for months, one for quarters, etc.  You could even get fancy and add a Year field, so you can view the aggregated numbers for previous years.