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?