2 Replies Latest reply on Oct 31, 2016 2:10 PM by philmodjunk

    Database Design help




      I'm hoping someone can help me.  I've tried again and again and I can't seem to get this right.  I want to construct the following database.  I've tried several ways in which to use a join table to no avail.  I believe it needs to be a many to many database.  I think there would need to  be 3 individual tables (reviewers, movies, and reviews).


      I want have one where multiple different reviewers can write various reviews including more than one on an individual movie on different movies.  Thus one movies can have multiple reviews by one reviewer and other different reviewers.  One reviewer can review multiple different movies including more than one review on an individual movie.  Any one review could only be by one reviewer on one movie.  On top of that I would like for a reviewer to be able to rate the movies 1-10 and then have each movie be able to have a calculated average score.

        • 1. Re: Database Design help

          A multi-to-multi table allows exactly what you need. It is not limited to only ONE join between Table A and Table B via the intermediate Table C. You can add as many intermediate records to Table C as you see fit.


          Three tables:





          Key Fields:






          Join the two foreign keys in "Reviews" to the PrimaryKeys of Reviewers and Movies. Done...


          Of course add your name fields to reviewers and movies. Add your score field and summary to the reviews table.


          To summarize a movie's average rating from all reviewers you can create a calculation type field and the Average() function:

          Movies::AverageReview = Average( Reviews::Score )


          You can invert that to get the Reviewers average for all movies:

          Reviewers::AverageReview = Average( Reviews::Score )


          If you want to get a more specific summary combining movie/reviewer, you can do so in a number of ways:

          1) ExecuteSQL calc function, EG:

          ExecuteSQL(SELECT AVG(Score) FROM Reviews WHERE ReviewerForeignKey = ? AND MovieForeignKey = ?";"";""; Reviewers::PrimaryKey ; Movies::PrimaryKey )

          2) Or by adding a summary type field to Reviews that is an average of the Score field, and use get GetSummary() function to break it apart by movie.


          3) Create an additional Self-join to a table occurrence of the reviews table (ReviewsSelf) based on:

               Reviews::ReviewerForeignKey = ReviewsSelf::ReviewerForeignKey

               Reviews::MovieForeignKey = ReviewsSelf::MovieForeignKey

               Then create a calculation field of: Reviews::ReviewerScore = Average( ReviewsSelf::Score )


          Don't forget as well that you will need to come up with a way to create the records in the intermediary table. You can do so via a portal on Reviewers by:

          -Checking the box on the relationship between Reviewers and Reviews to allow creation in the reviews table.

          -Adding a "reviews" portal to the Reviewers layout.

          -Adding Reviews::MovieForeignKey to the portal

          -Creating a 2-part popup value list on MovieForeignKey that has Movies::PrimaryKey as the first field and Movies::Name as the second field.


          Thus when someone selects a value in the empty portal row in the MovieForeignKey field, it will auto-fill Reviewer::PrimaryKey into Reviews::ReviewerForeignKey and create the relationship with the Movie you selected.

          • 2. Re: Database Design help

            Seems to me that the three tables that you describe are all you need here. Your reviews table can serve as the "join" table between movies and reviews.



            Reviewers::__pkReviewerID = Reviews::_fkReviewerID

            Movies::__pkMovieID = Reviews::_fkMovieID


            While a portal is not the only option for working with these relationships, a portal to reviews on the Reviewers layout can list all reviews (and movies reviewed) created by that Reviewer. Put the same portal on the Movies layout and you can list the reviews (and reviewers) created for that movie.