Database Design: Intersection of three fields
This is more of a database design question for my Convex app than a Convex-specific question. I am building a platform for hosting and participating in hackathons, and I have three tables to store participation, in addition to the users table:
- teams, which takes a competition ID
- competition
- participants, which takes a team ID and a user ID
I designed it this way to adhere to database normalization, but I am trying to use Convex indexes to
- quickly get the user's team for a particular competition
- quickly get all the teams for a particular competition
But I can't index teams with a user field, so I am stumped. Any ideas?
- teams, which takes a competition ID
- competition
- participants, which takes a team ID and a user ID
I designed it this way to adhere to database normalization, but I am trying to use Convex indexes to
- quickly get the user's team for a particular competition
- quickly get all the teams for a particular competition
But I can't index teams with a user field, so I am stumped. Any ideas?
