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:
- Client listings (for example csv generations for various 3rd party services)
- Change banners to website for various promos
- 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)