I think you can get away with one pivot table (challenge_participants) and add the creator in the challenge table (creator_id = user_id who created it)
Multiple Pivots?
Hey, what would be the best approach for this scenario...
So I want users to be able to create a challenge, then others can take part in this challenge. however the owner can also take part in the challenge too, and also then share ownership of the challenge, like a simple acl, e.g. I create a challenge, but my friend can modify it as well.
So to start with I'm thinking I would have a users table, and a challenges table...
So then would i create two pivot tables tables, one for challenges_users eg users who have create a challenge, and one for challenges_participants, users who are participating in a challenge?
Or have one pivot table to store ownership/participant info on the pivot table itself? Like user_id, challenge_id, relation_type.
Please or to participate in this conversation.