many-to-many: store and query
I have a use case where there are about 60 possible choices, and each user can select between 1–60 of them.
Currently I’m storing them in a separate join table like:
{ userId, choiceId }
This works for filtering (e.g. “find users who selected X”), but with 10k users it could mean 100k–600k+ rows, which feels heavy.
I’m considering other approaches:
Store choice IDs in an array inside the user document. (But then how to query efficiently by choice?)
Use a bitmask (60 choices = fits in a 64-bit int) for compact storage. But Convex doesn’t seem to support bitwise queries directly.
A hybrid approach — store array/bitmask in the user doc for compactness, and maintain a separate index table for filtering.
What’s the recommended way in Convex to store and query this kind of many-to-many preference data efficiently?
Currently I’m storing them in a separate join table like:
{ userId, choiceId }
This works for filtering (e.g. “find users who selected X”), but with 10k users it could mean 100k–600k+ rows, which feels heavy.
I’m considering other approaches:
Store choice IDs in an array inside the user document. (But then how to query efficiently by choice?)
Use a bitmask (60 choices = fits in a 64-bit int) for compact storage. But Convex doesn’t seem to support bitwise queries directly.
A hybrid approach — store array/bitmask in the user doc for compactness, and maintain a separate index table for filtering.
What’s the recommended way in Convex to store and query this kind of many-to-many preference data efficiently?
