0

In my case I have a table named clients that follows the following information:

store_listing:
#######
id SERIAL PK
title VARCHAR(100) not null
features JSONB not null
comments TEXT
vat_num VARCHAR(100) NOT null

This table I use to generate:

  1. Client listings (for example csv generations for various 3rd party services)
  2. Change banners to website for various promos
  3. Enable and disable various features that client has access to.

A typical value can be:

{
   banner_cats:{
      active: true,
      types: ['persian','aegean']
   },
   promo_petfest_2023:{
     active: true,
     meta: ['dogs','cats','giraqffes']
   },
   1bank_catzoo: {
     active: true
   },
   feature_virtual_pet: {
     active: true
   }
}

Each time I need to flag and place meta to a specific Store_listing I need to update the features column. Also in my case promos can be generated on the fly according for example a big organization (e.g. WWE) wants to make a promo where will place special banners to my website Therefore I'll have to update the integration column like this:

{
   banner_cats:{
      active: true,
      types: ['persian','aegean']
   },
   promo_petfest_2023:{
     active: true,
     meta: ['dogs','cats','giraqffes']
   },
   1bank_catzoo: {
     active: true
   },
   feature_virtual_pet: {
     active: true
   },
   wwe_wild_beast_payperview:{
      active: true
   }
}

That seems like a bad decision to me therefore I thought this approach:

features_and_promos_settings
####
listing_id FK store_listing
identifier: String (lowercase, does not contain spaces)
active: boolean
meta: jsonb
type: ENUM(PROMO,FEATURE,BANNER) NULL
#####
PK (listing_id, identifier)

So in my case the integrations column will contain this value (listing_id = 1) :

{
   banner_cats:{
      active: true,
      types: ['persian','aegean']
   },
   promo_petfest_2023:{
     active: true,
     meta: ['dogs','cats','giraqffes']
   },
   1bank_catzoo: {
     active: true
   },
   feature_virtual_pet: {
     active: true
   },
   wwe_wild_beast_payperview:{
      active: true
   }
}

Will turn into records in features_and_promos_settings

listing_id identifier active meta type
1 banner_cats true {types: ['persian','aegean']} BANNER
1 promo_petfest_2023 true {meta: ['dogs','cats','giraqffes']} PROMO
1 1bank_catzoo TRUE NULL NULL
1 feature_virtual_pet TRUE NULL FEATURE
1 wwe_wild_beast_payperview TRUE NULL BANNER

But would this approach will benefit me in cases such as searching and resource usage during select. The RDMS that I'll use is postgresql-11.

I expect that may reports will be generated for KPIS (e.g. to answer questions How many listings did participate in a specific promo)

1 Answer 1

1

I'd say that splitting up the JSON array into a set of rows in a table is certainly a good idea. Your updates will become faster, and your queries won't become slower.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.