Suggestions on schema best practice?
Bringin my data from postgres, this is one model I've been using and I'm wondering if this would not be better in convex as arrays. I'm coming from an SQL background with the usually weight on normalization.
I have a people table, a phone_number_types table, and a person_numbers table linking the two with fireign keys (what is convex-speak for primary and foreign keys? same thing? Convex doesn't specificy foreign keys in their tables, right?). This allows for a given person to have one or more phone numbers attached to them with different types. Some people will only have one number, others will have several. I didn't want my people table to have fields for 5 possible types of numbers most of which would be empty for most people. I also have the same structure for email addresses and physical addresses.
I'm wondering, from a convex perspective, is it better to store these kinds of things in array fields in the people table? It would mean far fewer relational joins, but I read in one of the docs it is better to not have too deeply nested of array fields. The array might look like
{phone_numbers: {home: xxxxxxxxxx},{mobile: yyyyyyyyyy}}
I actually had this a year or so ago when I was playing with MongoDB. Is it a matter of preference in this case since its really only a two level deep array?2 Replies
Thanks for posting in <#1088161997662724167>.
Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets.
- Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.)
- Use search.convex.dev to search Docs, Stack, and Discord all at once.
- Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI.
- Avoid tagging staff unless specifically instructed.
Thank you!
both schemas are possible with convex (just as they're both possible with sql or mongo).
you can make foreign keys by storing fields of type
v.id("person")/v.id("phone_numbers"), and do the join with ctx.db.get or other queries. See https://stack.convex.dev/functional-relationships-helpers for that pattern.
it's also reasonable, especially if the array is small, to store arrays of json objects. document sizes are limited to 1MiB https://docs.convex.dev/production/state/limits#documents but if the array has at most 5 numbers, you'll be totally fine.
the advantage of foreign keys is you can have unbounded data, and there are more possible access patterns: you could efficiently look up a person by their phone number, for example. the advantage of arrays is it's a bit faster (each ctx.db.get takes about 1ms).Database Relationship Helpers
Traverse database relationships in a readable, predictable, and debuggable way. Support for one-to-one, one-to-many, and many-to-many via utility func...
Limits | Convex Developer Hub
We’d love for you to have unlimited joy building on Convex but engineering