CODEWITHSUNDEEP
database
Eric
Eric

Reputation: 2705

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

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.

Upvotes: 0

Views: 236

Answers (3)

rolodex
rolodex

Reputation: 530

Having an extra field just for the name in the table may cause you trouble if you're looking into multiple languages.

Here's how I'd do it

categories


id | name
==================
1  | OIL_CLEANSER
2  | CLEANSER
3  | TONER

Then I'll have a separate files for the translations, format is depending on which library/framework you're using. Data will be returned as is.

translations.en.json

{
    "OIL_CLEANSER": "Oil cleanser",
    "CLEANSER": "Cleanser",
    "TONER": "Toner",
}

translations.zn.json
translations.my.json
...

Frontend code will just render the translations instead. This way the truth is always in the DB, what's rendered is always the translations, and you can pass the translation files to translators to add/update accordingly.

e.g

translated = translation[curr_lang].json

for cat in categories
   console.log(translated[cat.name])

Upvotes: 0

Elijah
Elijah

Reputation: 2213

How is it a worse practice to store the sortKey and UI label in the database than having an entire copy of the products in the UI codebase? The sortKey looks to be correlated to the IDs and the label could be algorithmically produced using:

name = name.charAt(0) + name.slice(1).replace('_', ' ').toLowerCase();

Upvotes: 0

user16930239
user16930239

Reputation: 9798

You can do this:

table

Add two columns to category table, one for the name in UI and one to sort the category in the UI, this way you can get the UI name and change the order (sortkey), also you can add more languages in the future.

I hope I am understanding this well.

Upvotes: 1

Related Questions