Understanding Related Tables
I have been creating databases in FileMaker for quite some time but I have only recently started to try to 'really' develop a database in FileMaker. I understand the basic concept of related tables but have questions about the actual practice of developing a related table database. I'm hoping someone can help shed some light.
To help me understand, I've created a simple database to illustrate the points I'm stuck on.
The database is designed to create a simple weekly work schedule.
This table has a record for each day of the week, Monday thru Sunday, so seven records.
Each Day record has a field for HourStart and HourEnd and DayID.
This table will have a record for each week of the year, 01 thru 52. This table starts out with no records.
Each Schedule record has a field for WeekNumber and ScheduleID.
The Days and Schedules tables will be related in a one-to-many relationship: ScheduleID ---> DayID
The idea is that any one Schedule record will have many Day records, but any one Day record will only have one Schedule record.
Here's where it gets a bit confusing for me.
The goal is to create a new Schedule record for Week01 and select from the list of Day records which days will be worked and enter the start and end hours for that day of that week. How is this done? I can think of a couple of ways.
Option 1: Separate Table
I create a duplicate Days table (DaysUsed) that will hold all of the Day records that are used in the schedules. I would then have to create a script that, when invoked, would copy the selected Day record data into a new record in the DaysUsed table, assigning the appropriate ScheduleID.
The messy thing about this is that if I change any fields in the Days table, I would need to make matching changes to fields in the DaysUsed table.
Option 2: Separate TO
I create a new table occurrence of the Days table to hold all of the Day records that are used in the schedules and create a new RecordType field to identify if the Day record is a 'master' or a 'used' record. I would still need to create a script to create a new Day record with a copy the record data of the selected record that has the RecordType set to 'used'.
The nice thing about this is that it seems like it would be cleaner. Since both the Days TO and DaysUsed TO are both the same table, any field changes would show up in both of them.
Do I have any of this right so far? Because I have a followup question about how I would go about setting up a Schedule layout that would include a list of the available Day records that could be 'checked' for inclusion in that week's schedule?
Thanks to anyone that can help shed some light on this. I would appreciate it.