Primsa.io and Expanding my DB Model with sub-types

by Corey Snyder

Right now I have this pretty awesome and fully featured app built on Prisma@1.34.0. As part of trying to be LEAN and get it running quickly I have purposely only focused on a single Product Type, Flight Controllers. The plan was never to stop there, but expand to other Types like: Motors, Receivers, Cameras, and maybe 10 other product types. I'm now at that point where it's time to expand and I am waffling on how to do this with the Prisma DataModel definitions available.

Let me start by showing you my current datamodel.prisma. I wanted to prune it down to reveal only what's relevant to the question but I worry in doing so, I take away context that could be important. So I apologize for it's length but here it is:

The Problem I ran into with just adding a new type Motor{} is when it comes to linking to the type MerchantLinks. A MerchantLink is essentially a way to attach a FlightController to a Merchant(store) with fields like price, link, availability. And as part of my DB Model I have it setup with relationships in both ways, with the purpose of being able to go to a Merchant page and see all of the products they offer. This means having a field called flightController on type FlightControllerMerchantLink. I cannot rename these to a product field on type ProductMerchantLink because product cannot be setup as product: FlightController || Motor || Battery || Camera || .... I can only point it at a single type.

Ok, so I'm not DB Design Engineer, and it's probably one of my weaker engineering skills so this is where I turn to colleagues and the internet. In doing so, as far as I can tell I have two options to get around this.

I think I could create a bunch of the type {ProductType}MerchantLink, one for each product type. And then link all of those as optional fields on the merchant, like so:

The thing I dislike about this approach is that it's not normalized. I'm repeating columns across many tables, such price, url, inStock, postedBy, etc repeated across all of the {ProductType}MerchantLink. Also, I'm not sure if this will have any ramifications that will block me from doing what I want to achieve in terms of site products, pages, or features. I've not taken this path yes, as I opted for option 2 instead. So I'll describe that now.

Option 2, Replace Flight Controller with Product and have a bunch of optional fields to store all the product-type-specific data

After talking with my co-worker he mentioned that he's solved this type of problem before by having a single type Product with a field of Type which describes what type it is, and then bunch of fields which point to the specific ProductType, where only one would be set at a time. Granted he did not implement something like this in Prisma, but I think it could still work. Like so:

With this setup, all of the linked Types for things common to all products, all link off of the type Product. These things include: Images, Product Reviews, Ratings, and Merchant Links. All of the fields common to all products can be added directly like cheapestPrice, averageReview, createdAt, etc.

I set about testing this new schema with a single type, FlightController and it seemed to work. So I pushed forward and expanded to 10 different product types.

Here's how my data model looks now

And here's my mutation resolvers for adding/editing products. NOTE: The code definitely needs cleaned up but I got it to a point that it works!

One of the benefits of going this route of making everything a base Type Product is that I can add/edit/delete any product type with a single mutation endpoint. I was also able to update my FE code for all of the pages I had setup for just flight-controllers to be retrofitted to work with the other product types.

This includes:

  • Product List Page
  • Product Details Page
  • Product Edit Page
  • Product Search Component
  • Add/Edit Images Component
  • Add/Edit Reviews Component

I'm not sure if this approach will cause issues down the road but it seems to be working for now.

One thing that I thought for sure wouldn't work but was pleasantly surprised on, was that my product list page queries still work, with a little modification. I'm able to sort/filter at the Product and Product.SubType (Product.flightController) levels in a single Prisma query like so. A key thing to note here is that I'm doing an orderBy at the Product level but I'm doing filtering where clause at the Product.flightController level.

Fast forward 2 weeks and this approach is still going strong. I created a new feature which allows the user to build an entire drone attaching all of the common components such as Frame, Motors, Flight Controller. The components are all of type Product, with product-specific sub-types. I plan to continue heading down this path and I'll report back if I run into any issues.