3 Replies Latest reply on Aug 26, 2013 10:00 AM by philmodjunk

    join table vs star join

    layotte

      Title

      join table vs star join

      Post

           I am developing a database for a school.  There are 3 main tables - students, activities and expectations.  A student can have many activities and an activity can have many students.  Each activity can have many expectations and expectations can belong to many activities.  I started with 2 join tables student to activities and activities to expectations but then was not able to assign a level to each expectation for each student.  Would a star join in this case be a better way to proceeed?  Any help would be appreciated.

        • 1. Re: join table vs star join
          philmodjunk

               When reading your description, this possible set of relationships popped up in my brain:

               Students-----<Student_Activity>-----Activities------<Activity_Expectation>-----Expectations

               That matches your original description, but then you said:

               

                    was not able to assign a level to each expectation for each student

               And yes, that needs a "star join" (which is still a "join" table BTW) in order to add data for each Student-Activity-Expectation combination.

               Students-----<Student_Activity_Expectation>-----Activities
                                                             v
                                                             |
                                                Expectations

               But keep in mind that these are not mutually exclusive design options. Using multiple Tutorial: What are Table Occurrences? and different layout designs, you can use the first set of relationships to assign a specific list of expectations to each activity and the star join to list the "levels" in the star join table. And a script can use the first relationships to get a list of expectations in order to generate sets of Student_Activity_Expectation records for each student for you to avoid tedious and error prone data entry tasks.

          • 2. Re: join table vs star join
            layotte

                 Thanks for the quick reply.  I have been trying to accomplish what you propose.  I have the first 2 join tables set up (Student_Activity and Activity_Expectation) and I am able to populate them through portals.  Now I am not sure how to generate the Student_Activity_Expectation records.  Can you point me in the right direction?

            • 3. Re: join table vs star join
              philmodjunk

                   I mentioned that a script could be used to create those records.

                   A script can pull up a found set of the students linked to that activity on one layout and the list of expectations linked to that activity on another layout.

                   A nested loop can then use the data in both found sets to create a set of new records in the star join table with the correct links to students and expectations.