DB model strategy to override field value

Question

We have to store some dynamic configuration in JSON format in DB. There would be some default configuration, and it can be overriden per account. Can someone help me with the strategy to store such kind for data.

I have come up with following. If the accountConfig is null then look at defaultConfig.

DefaultConfig table

id, name,   config
1   name1   {x:12, y: 13}
2   name2   {y:10, z:100}

AccountConfig table

id, accountId, defaultConfigId, accountConfig, active
1   1000       1                null           false
2   1000       2                {y:11, z:111}   true

Should I have inheritance, or embedded entities? or anything else?

Answer 1

You can create a view like:

create view all_config as
select y.id, x.accountId, y.config
from accounts x
cross join defaultconfig y
where not exists (
    select 1 from accountconfig z
    where x.accountid = z.accountid
      and y.id = z.defaultconfigid
)
union all
select a.id, a.accountid, a.defaultconfig
from accountconfig a

and select from there to get the current config for an account. Note that you should only store information that is changed in AccountConfig. Don't use null as a merker. In your example, id 1 in AccountConfig should be removed. The view will provide this info from the default table.

Answer 2

If I understood correctly, you can check for following design model:

Config

config_id 
account_id
name
config
is_default
is_active

I hope it will help you, Thanks.