3 Replies Latest reply on Jul 31, 2014 11:28 AM by philmodjunk

    Understanding Related Tables

    AjEGfmTech

      Title

      Understanding Related Tables

      Post

           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.

      Days table
           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.

      Schedules table
           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.

        • 1. Re: Understanding Related Tables
          philmodjunk
               

                    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.

               I'd set that up like this to avoid confusion in the field names:

               Schedules::__pkScheduleID = Days::_fkScheduleID

               I whether you use the __pk, _fk labeling for primary and foreign keys, I strongly recommend that you not name the match field in Days the "DayID" as this field does not identify the day (up to 7 records will have the same value in this field), but the schedule to which it is linked.

               I suggest that you use a script to create your new schedule that when performed creates 1 new record in Schedules and 7 new related records in Days already linked to that new schedule record. This script can create the new Days records completely from data in the script or by looping through a designated set of 7 days records or from the 7 days records for the previous schedule. This is essentially an elaboration of your second option.

          • 2. Re: Understanding Related Tables
            AjEGfmTech

                 Thanks Phil. I will definitely change the ID field name to avoid confusion with the keys.

                 So Option 2 (same table, separate TO) is an acceptable way to set this up? Good, I'm glad. That made more sense to me but I wasn't sure if that was frowned upon for other reasons that may be more evident later.

                 I understand what you mean about a single script to create the new Schedule record and 7 new related Days records, however, that is not the use case that I'm trying to develop. I would like to understand how I could give the user the ability to select WHICH of the available 'master' Days records they would want to include in the new week's Schedule record. I'm currently stuck at figuring this out?

                 - How do I display the list of available Day records? A portal, I presume?
                 - But then, how do I allow for a 'checkbox' method of identifying which Day records to include in the new Schedule record?
                 - With that in mind, what if the user wants to go back and add or delete Day records to that specific Schedule record?

                 These questions seem to me to all relate to each other in regards to how this is setup. Again, this is just a pretend 'simple' example that I hope to use to better understand what I'm really going to be working on.

                 Thanks for your help.

            • 3. Re: Understanding Related Tables
              philmodjunk
                   

                        - How do I display the list of available Day records? A portal, I presume?

                   A portal can be used or they can be displayed as a list view showing a found set of the desired records.

                   You can put a button in each portal row or (if using a list view) the body of the layout where clicking that button selects that record.

                   

                        - With that in mind, what if the user wants to go back and add or delete Day records to that specific Schedule record?

                   "Selecting" a record should generate a new record in Days that starts as a duplicate of the selected record. Thus deleting a record should not delete the original "master" record but the new record created from it.