dannyelo•6mo ago

Understanding Convex Database Design Patterns

Hello, I'm new to Convex, and have some questions about design patterns. I've a listings table (title, category, etc...) I want to have variants of that specific listing like color, size, other Should I create a "listingVariants" table? or I can insert in a document property as an array? I'm not sure if I'm need to work as a relational or non-relational database. Then each listing variant needs to have multiple files (images) attached. Should I create another table "listingVariantImages"? (with the listingVariantId of the table mentioned before). Thank you!

6 Replies

lee•6mo ago

you can do either of those designs in convex. if you store it in an array, that makes it easier & cheaper to fetch a "listing" all at once, with all of its variants and images. but it does restrict the number of variants and images, since each document has size limited to 1MB. it also restricts how you can do lookups: if you want to only look up the images for a single listing&variant, that would not work because the images are all together in the single document. if you want no size constraint, and you want versatile query semantics (including pagination, in case there are tons of images, for example), you can do the "listingVariants" and "listingVariantImages" relational method.

dannyeloOP•6mo ago

Thank you Lee, Should be a good idea create an array inside the listings table with all the variants, and only create a separate table for the images referencing each variant? Does the elements in the array have unique id's so I can reference them in the images table?

lee•6mo ago

Should be a good idea create an array inside the listings table with all the variants, and only create a separate table for the images referencing each variant?

sounds like a good idea to me, but it depends on how big the variants are and how many there are.

Does the elements in the array have unique id's so I can reference them in the images table?

you can put whatever you want in the array, e.g. an object {variant: "color", value: "blue"} and then identify the items by the variant field. You could also identify array items by index, if the indexes are stable.

dannyeloOP•6mo ago

Do you have an example of identifying the items by index? What do you mean about stable indexes? I may have up to 30 to 40 variants, and the can be "color", "size", etc..

lee•6mo ago

if you have a listing like

{ _id: "def", title: "a", category: "xyz", variants: ["blue", "large"] }

{ _id: "def", title: "a", category: "xyz", variants: ["blue", "large"] }

then you can have a listingVariantImages like

{ imageId: "abc", listingId: "def", variantIndex: 0 }

{ imageId: "abc", listingId: "def", variantIndex: 0 }

and this image will correspond to the listing's first variant, which is "blue". This doesn't seem like a great design though because you probably want the variants to have arbitrary order (so their indices aren't stable). So it's better to do

{ _id: "def", title: "a", category: "xyz", variants: [{ variant: "color", value: "blue"}, {variant: "size", value: "large"}] }
{ imageId: "abc", listingId: "def", variant: "color" }

{ _id: "def", title: "a", category: "xyz", variants: [{ variant: "color", value: "blue"}, {variant: "size", value: "large"}] }
{ imageId: "abc", listingId: "def", variant: "color" }

or you could give up on the array and use an object instead

{ _id: "def", title: "a", category: "xyz", variants: { color: "blue", size: "large"}] }
{ imageId: "abc", listingId: "def", variant: "color" }

{ _id: "def", title: "a", category: "xyz", variants: { color: "blue", size: "large"}] }
{ imageId: "abc", listingId: "def", variant: "color" }

dannyeloOP•6mo ago

Thank you, very useful info. Sorry, one more question. What do you think about this structure? listings (table)

  {
    id,
    title,
    category,
    etc...
  }

  {
    id,
    title,
    category,
    etc...
  }

listingVariants (table)

  {
    id,
    listingId,
    name, // color, size, etc.
    value,
    images: [ 
              { imageIndex: 0, url: 'https...'},
              { imageIndex: 1, url: 'https...'},  
              { imageIndex: 2, url: 'https...'},  
            ]
  }

  {
    id,
    listingId,
    name, // color, size, etc.
    value,
    images: [ 
              { imageIndex: 0, url: 'https...'},
              { imageIndex: 1, url: 'https...'},  
              { imageIndex: 2, url: 'https...'},  
            ]
  }

Understanding Convex Database Design Patterns

Did you find this page helpful?