Schema design for B2B site with varying products using Keystone 6

Question

I am building a B2B wholesale site using Nextjs and Apollo Client for the front-end, with Keystonejs running the backend. This question is more for the backend and setting up the schema for Keystonejs.

This site is based off of a tutorial from Wes Bos, https://advancedreact.com/. I was hoping to expand upon the idea and have been mostly successful thus far. Until I realized that product entry was not going to be as easy as his example.

First off excuse my ignorance with the backend of this project as I may use the wrong terminology. I say schema but maybe I mean database design? I know Keystone refers to them also as lists. So Product, CartItem, Order are all lists. Like so:

export const Product = list({
  fields: {
    name: text({ validation: { isRequired: true } }),
    slug: text({ isIndexed: 'unique', label: 'Pretty URL)'}),
    hotdeal: checkbox({ label: 'Hot Deal?' }),
    inventory: decimal(),
    price: integer(),
    category: relationship({
      ref: 'Category.product',
    }),
    photo: relationship({
      ref: 'ProductImage.product',
      many: true,
      ui: {
        displayMode: 'cards',
        cardFields: ['image', 'altText'],
        inlineCreate: { fields: ['image', 'altText'] },
        inlineEdit: { fields: ['image', 'altText'] },
      },
    }),
  }
});

My products are going to have several categories. And within each category products will have different fields in Keystone. For example we may have a category for laptops. Then another category for t-shirts. The product fields for the laptop may be:

• Brand
• Model
• Name
• Price
• CPU
• Memory
• Screen size

T-shirt product fields may have:

• Name
• Size
• Color
• Material
• Price

You can see the only common fields they share are Name and Price. So having a schema for just product didn't work. Because filling out a form for a laptop that shows size, color and material wouldn't make sense. Nor would seeing a field for CPU/memory/screen size make sense when entering a new t-shirt.

I thought I could create separate schema for each category. So, the unique fields for each category did not show up in other categories. Then I would create a main Product schema file that had a relationship with each category. But to me it just sounds overly complex and not scalable as new categories may be added.

So how do I go about setting up the schema in a way that makes sense for unique products, so that product entry within Keystone isn't just a never-ending form with every possible product field? But a flexible and scalable approach. Maybe my tool is limited, and it just isn't possible with Keystone.

NOTE: This is not a full-blown ecommerce site. No transactions or sales will be made online. It will just handle the orders.

Here is a link to my repo. https://github.com/brudolph/green-mountain-cannabis/tree/main/backend

UPDATE: So with the answer @Molomby gave it had me thinking on how the relationship between the parent class Product and the subclasses, ShirtProduct and LaptopProduct woul;d work.

So like you mention @Molomby I have the Product class and ShirtProduct and LaptopProduct subclasses. With each subclass referencing the Product class through the relationship field. But it's just a one-sided relationship. To make it two-sided so Product knows about the subclasses it seems I will need a relationship field for each subclass in Product (see example below). Am I correct in that assumption? Which doesn't sound very scalable. My client mentioned being able to add new categories which is possible since I have a Category list but then I would need to create the new category class each time. So for example he adds "dog-food", I would need to create DogFoodProduct class.

export const Product = list({
  fields: {
    name: text({ validation: { isRequired: true } }),
    slug: text({ isIndexed: 'unique', label: 'Pretty URL)'}),
    hotdeal: checkbox({ label: 'Hot Deal?' }),
    inventory: decimal(),
    price: integer(),
    category: relationship({
      ref: 'Category.product',
    }),
    shirt: relationship({
      ref: 'ShirtProduct',
    }),
    laptop: relationship({
      ref: 'LaptopProduct',
    }),
    dogFood: relationship({
      ref: 'DogFoodProduct',
    }),
    photo: relationship({
      ref: 'ProductImage.product',
      many: true,
      ui: {
        displayMode: 'cards',
        cardFields: ['image', 'altText'],
        inlineCreate: { fields: ['image', 'altText'] },
        inlineEdit: { fields: ['image', 'altText'] },
      },
    }),
  }
});

Answer 1

TL;DR: This is a common problem in data modeling. If you have time to build one, Keystone's custom field types are the solution I'd suggest for this use case. See my notes towards the end.

---

The issue you've struck on is a well recognised problem when attempting to map a class hierarchy to tables in a relational database (or lists in a Keystone schema, which is effectively the same thing). I've actually written about it before in the context of user types.

In this case you have a Product class with LaptopProduct and ShirtProduct subclasses. Whether you're thinking about these as classes in JavaScript/TypeScript is besides the point – conceptually they're still related classes of things. You want to treat all products alike sometimes but shirts and laptops have behaviours or data specific only to them.

There's few ways of coming at it:

Single Table Inheritance

A single "wide" table (or Keystone list) with the all fields needed across all subclasses. Then, when you're dealing with subclass specific behaviour, you just ignore all the properties you don't care about.

Sounds like you've already considered this and ruled it out:

So having a schema for just product didn't work. Because filling out a form for a laptop that shows size, color and material wouldn't make sense. Nor would seeing a field for CPU/memory/screen size make sense when entering a new t-shirt.

And yeah, it can work ok for a small number of subclasses or where the difference between subclasses is small but generally it doesn't scale well. In addition to having a super bloated form in the Keystone Admin UI, you'll also get conflicts when naming different fields. Say you wanted to add a color field for laptop with different options to the shirt color field, you get a conflict. So maybe prefix all your fields with the subclass to avoid conflicts (so shirt_color and laptop_color)? But then you want to add a HoodieProduct that does use the same shirt colors... it gets complicated quickly.

Multiple Table Inheritance

A single table/list for the parent class that includes the common fields, then another table/list for each subclass.

This is the second option you mention:

I thought I could create separate schema for each category. So, the unique fields for each category did not show up in other categories. Then I would create a main Product schema file that had a relationship with each category. But to me it just sounds overly complex and not scalable as new categories may be added.

It's a bit more complicated than the "single table" approach but more scalable – you're less limited by the number of subclasses and the difference between them. In Keystone your Admin UI experience will still be a bit clunky as you'll need to click through to the category-specific fields in the other lists (unless you customise your Admin UI to avoid this). The database also has to do slightly more work as it needs to lookup in two tables to get a single product. Still, it's nice that you have a single productId and all your common fields in once place.

Concrete Table Inheritance

In this solution you completely split the subclasses apart, duplicating the common schema to each one, like this. In your example, this would mean having LaptopProduct and ShirtProduct lists but no shared Product list.

This would arguably give you the best "out of the box" Admin UI experience (as each product category would have a single create/edit form, with the correct fields) but it makes a lot of other things harder as it prevents you from really doing anything with "products" in the generic sense. A list like Orders for example, now needs to link to each product category separately. If you're using CUIDs for your primary keys (the default in Keystone), your product IDs will still all be unique but everywhere you reference one you also need to record its category so you know which list to look it up in.

---

Those are the classic solutions to the problem. Unfortunately, Keystone doesn't have a simple out-of-the-box solution to completely solve this dilemma but it does give you some additional ways to work around it:

Json Field

If you're ok with your category-specific fields being a bit looser you could use a Json field. Out of the box this is going to appear in the Admin UI as a text area but it would still be possible to add validation based on the product category selected. So, for example, when a new shirt product was created you could use a hook to default the field contents to:

{
    "size": "M",
    "color": "white",
    "material": "cotton"
}

This would at least give you a bit of structure to work within while editing.

Custom Fields

Improving on the above, Keystone lets you create custom field types that could do a good job here. You could, for example, extend the Json field type but customise how it was rendered in the Admin UI to use real fields for the different options. In this scenario the "common" product fields and a category field would still be in the main list schema then all the category specific stuff would be implemented by the field type.

Today, this is probably the best solution for the use case you've outlined.

Future Tech

The Keystone team is working on a way of combining the flexibility of Json fields with graphQL typing to validate and enforce a particular structure and generated forms in the Admin UI. Its referenced  here and mentioned in the roadmap:

Nested fields

Sometimes you need to manage data in structures that are nested and/or repeating. We’re working on a way to define these in schema and have them stored as JSON field in the database.

There's currently no ETA but, if you're interested, follow the project on GitHub to be notified of releases.