best way organise these 3 models in DB

HI,

what would the best way to organise these models

I have 3 models (db tables) user activity question

A user can have many activities Activities can have many users

An activity can have many questions A question belongs to 1 activity

A user can answer to many questions A question can be answered by many users

I need to track which users have completed which activities I also need to track the answers to the activity questions

I think I need 2 pivot tables but I am not entirely sure... maybe it can be done with 1 pivot table

I think I would have a pivot table with user_id, activity_id columns to track which user has taken which activity

and another table with User_id, Question_id, answer columns to track the answers

Using this layout is there a way to to get the answers of a question for a user with one eloquent line of code?

can you guys let me know what you think?

Tray2

5 years ago

Level 73

A user can have many activities and a activity can have many users = Pivot
A question belongs to one activity = Foreign key (activity_id) in questions table
A user can answer many questions and a question can be answered by many users = Pivot

Depending on how the answer is done.

Multiple choices
Free text

If it's the first then you have an answers table that is connected to the questions table. One of these should be marked as the correct answer. Then the easiest is to add the answer_id to the pivot with the user and question.

If you use free text answers it's bit trickier but the basics are the same.

Please or to participate in this conversation.