#StackBounty: #database How to ensure consistency between lookup table in database and front-end code?

Bounty: 50

I have a lookup table for skincare product categories that looks like this (integer ID and string name)

enter image description here

There are only 9 fields in the table, populated using a seed script, since there are only so many categories for skin care products, although this table occasionally changes (for example, I might combine Oil cleanser and cleanser into one category).

I use this as a lookup table to specify the product category in product table.

These categories are also used in the UI code (e.g. to populate a dropdown), and for that I’m keeping a set of categories in my UI code as well. This feels duplicative and I would like to have a single source of truth.

const categoryLabelsAndOrder = {
  OIL_CLEANSER: { label: "Oil cleanser", sortKey: 1 },
  CLEANSER: { label: "Cleanser", sortKey: 2 },
  TONER: { label: "Toner", sortKey: 3 },
  // ...

However, I need to also somehow keep UI-specific logic (like label, which is the text that gets displayed on the UI or sortKey, which governs the order in which they are displayed), and it’s probably a bad practice to store these fields on the database.

If I forget to update them both in the database and the UI code, it can lead to bugs.

How do I fix this?

I looked into using enum types, but from what I found, they are supposed to be used for fields that are never going to change. enums do not completely solve my problem either.

Should I run some type of validation in the code for categoryLabelsAndOrder with the data fetched from categories table so that the keys in the object exactly match what’s in the categories? This also sounds superfluous, especially since I have to fetch from this table (and all other lookup tables) just to validate the consistency.

I am using Prisma & PostgreSQL, but this problem is agnostic of database solution or ORM.

Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.