Linking multiple many to many relationships

[Gavin Kenny]

Hi,

This is a bit of a noob question. I run a club for children, where we have a quiz night and I would like to develop a web2py app to speed up the scoring and create a leaderboard. I would like to use web2py as I love the elegant design, the portability and the focus on security, but I am not an expert web app or database developer.

The requirement is : 

1) Each child has a name and then groups of children form teams - standard one to many relationship

2) There are number of rounds and each round has a number of questions - again a one to many relationship

3) Each question will receive a mark it isn't just yes or no but a score out of 10.

4) I understand that the team to round relationship is a many to many relationship

But ...

How do I set up a model and controller, that allows me to record the scores of each individual question for each team? I am going round in circles as it feels like a many to many to many relationship?

Any guidance would be gratefully received.

Gavin

[Leonel Câmara]

You need another table which is an answer table, the answers will have a reference to the question and the team that answered.

[Gavin Kenny]

On Friday, 19 July 2019 15:26:05 UTC+1, Leonel Câmara wrote:

You need another table which is an answer table, the answers will have a reference to the question and the team that answered.

Thank you,

So to check my understanding, the Answer Table would have 3 fields, an answer field and then two reference fields, one for question.db and one for team.db?

Also,

The questions are not uniquely numbered but as part of the round, i.e. Round 1, Question 1, Q2, Q3, Q4 and Round 2, Q1, Q2, Q3.....  etc

Would I therefore link the answer table to the round table and then need some kind join to allow me to get to the question?

[Dave S]

On Friday, July 19, 2019 at 8:34:04 AM UTC-7, Gavin Kenny wrote:

On Friday, 19 July 2019 15:26:05 UTC+1, Leonel Câmara wrote:

You need another table which is an answer table, the answers will have a reference to the question and the team that answered.

Thank you,

So to check my understanding, the Answer Table would have 3 fields, an answer field and then two reference fields, one for question.db and one for team.db?

For small tables (tens to hundreds of entries), recursive selects might be the practical choice.
<URL:http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Recursive-selects>


 

Also,

The questions are not uniquely numbered but as part of the round, i.e. Round 1, Question 1, Q2, Q3, Q4 and Round 2, Q1, Q2, Q3.....  etc

Would I therefore link the answer table to the round table and then need some kind join to allow me to get to the question?

Good luck!

/dps