AnsweredAssumed Answered

Do I need 2 tables or 3?

Question asked by sb1_1 on Jul 10, 2009
Latest reply on Jul 10, 2009 by etripoli


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?