Inheriting/Overriding settings

[kicken]

Hey Folks

 

Looking for some opinions on how best to solve a particular problem that has come up a few times in various areas of an app I work on. Here's the gist of what I need to accomplish.

 

I need to create a setup in which various settings can be defined at multiple levels. Take three levels for example:

- Master

- Group

- Individual

 

The master level defines the system-wide defaults. For each group that is created, settings can either be "Use Defaults" which means use the master values, or customized for that group. The same principal applies to the Individual level.

 

In the past, I've generally solved this one of two ways:

1) Copy the settings from the previous level when an item is created. or

2) Leave the setting NULL in the DB for that level and do a JOIN for each of the other levels, using COALESCE to get the first non-null.

 

Neither feels particularlly great to me. They both have their own disadvantages

1) Changing the master doesn't change the other levels without some extra code to update everything else.

2) Can result in some messy queries due to the extra JOINs

 

Wondering if anyone has any better options they use when dealing with such a problem.

[KevinM1]

My SQL sucks, but why would you leave a value as null in the db? Why not simply give it some value by default (you can programatically set it during installation), and simply tweak it in your app settings as needed? Instead of having to walk the chain/join, you can just retrieve the setting level from the component directly.

[DavidAM]

Each of the methods you describe have their uses.

 

The first method applies the settings AS THEY ARE NOW when the record is created. So the parent levels may be changed without affecting existing children. If you WANT to affect children, you have the added complication of: how can you tell if the value of a setting is, say, "4" because it was inherited or because that is what someone wants it to be?

 

The second method keeps the settings inherited, regardless of changes to the parent levels. As to the complitcated query, I would consider creating a VIEW called "IndividualSettings" with the joins and COALESCE(). Then select from the View or JOIN to it if other data is needed from the Individual table.

[kicken]

My SQL sucks, but why would you leave a value as null in the db?

NULL works well, particularly in case #2 which for inheriting. As an example our current permissions system uses that setup, each permission group has a master view/edit/delete setting. Then overrides for various areas can be added and each override provides yes/no/default options for each of the view/edit/delete settings. In the query then I can easily check the setting for a particular area using:

SELECT
   COALESCE(ovr.CanView, mst.CanView) as CanView
FROM users u
INNER JOIN permissions mst ON u.PermGroupId=msg.PermGroupId
LEFT JOIN permission_overrides ovr ON ovr.PermGroupId=mst.PermGroupId AND ovr.AreaId=$AREA_ID
WHERE
  u.UserId=$USER_ID

If an override for the given area does not exist, or it's CanView is set to NULL (meaning use default) then mst.CanView will be returned instead of ovr.CanView thanks to COALESCE. This concept can apply to multiple levels, each level is just an additional JOIN and another entry in the COALESCE function. So far 3 levels as described in the first post is the deepest I've used it.

 

The first method applies the settings AS THEY ARE NOW when the record is created. So the parent levels may be changed without affecting existing children. If you WANT to affect children, you have the added complication of: how can you tell if the value of a setting is, say, "4" because it was inherited or because that is what someone wants it to be?

Indeed, each child record would need a IsCustomized field or similar. This case I've generally used more when changes to the master generally shouldn't affect child records, the settings are just inherited on creation.

 

 

The second method keeps the settings inherited, regardless of changes to the parent levels. As to the complitcated query, I would consider creating a VIEW called "IndividualSettings" with the joins and COALESCE(). Then select from the View or JOIN to it if other data is needed from the Individual table.

I've done a few views to try and help keep the end queries more manageable/readable. Some queries still end up being a bit big and a pain though. Perhaps there is a way I could re-structure the views to make them better, I'll need to evaluate that.

 

 

As for my current project, of the two methods #2 is probably the route I'll go. Since the issue has come up a few times I though I'd get peoples opinions on the matter and see if maybe there was a third option I hadn't thought of.