I have a scenario that I'm struggling to find a clean solution to. In my CakePHP app, I have a User and Survey model. The idea is that the admin can create surveys, and assign them to users. Users can then respond to the surveys (multiple times) and those responses get stored.
Now, I'm planning to make a Responses model, and associate the Responses with Users via hasMany/belongsTo. Easy. The problem comes in assigning Surveys to Users. Since users will be able to respond to multiple surveys, it makes no sense to try and add "survey_id" columns to my Users table for associations.
What makes the most sense is a new survey_assignments table, that has id, survey_id, and user_id columns.
The problem is I'm unclear how to handle that kind of intermediary association table cleanly, sticking to CakePHP best practices. What I need to do is fairly standard in straight php/mysql, but I want to avoid straight SQL queries, and keep things in line with CakePHP convention.
What's the cleanest way to go about creating and working with a (potentially model-less) association table, while avoiding manual queries?
