July 28, 2005 at 3:14 pm
In essence John and Alexey should inherit the same default settings.
Further to this Ann now joins the configuration but she gets her settings from John. When John changes his settings... Ann receives those changes. When the administrator's changes the default settings then neither John nor Ann inherits the settings, only Alexey.
July 28, 2005 at 3:26 pm
If John or Ann were to change settings, would the other one be affected?
K. Brian Kelley
@kbriankelley
July 28, 2005 at 4:14 pm
It sounds like a recursive Manager/Employee table...
I wasn't born stupid - I had to study.
July 29, 2005 at 2:05 am
I picked up this very interesting reference on this site back in April :
http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=123193
It may help with your problem.
July 29, 2005 at 7:10 am
If John or Ann were to change settings, would the other one be affected?
To Brian...
Because Ann receives (or inherits) her settings (instructions) from John , only Ann is affected when John changes his settings. The reverse is not true.
July 29, 2005 at 7:30 am
For each attribute, you either can make it nullable (if null then inherit) or have a parallel field to serve the same purpose. The nested sets tree model to which I gave a reference above should be useful in this context.
Using the parallel (rather than nullable) field option, you could make it point at the current 'controlling' user for that attribute. When someone customises their setting, it then would be necessary to modify this field accordingly on all child nodes. The nested sets structure allows you to do this with a single set based update making it very fast.
The record for each user (one attribute) comprises UserID, CustomValue, ControllingID
If ControllingID is zero, use CustomValue, otherwise use CustomValue of the user whose UserID = ControllingID.
When a user sets their CustomValue for an attribute, adjust the corresponding ControllingID of all child nodes where ControllingID is set and points to a parent of the user who has set an override.
In case you have a problem with the reference URL, the nested sets model gives each user an id range (ie two values). If my range is, say 10-20,
then anyone with a range that fits inside this (say 11-14) is a child and anyone whose range encompasses mine is a parent. This approach has some cost when adding users but avoids tree navigation and the need for cursors.
July 29, 2005 at 7:35 am
I agree with the recursive table structure concept. You'd have something like this (very simplistic -- will need some work!)
FLD: UserName
FLD: SettingsBasedOn (this field is linked back to the UserName field of another record)
FLD: Setting1
FLD: Setting 2
...
Then you'd have records like:
UserName: John
SettingsBasedOn: administrator
UserName: Alexey
SettingsBasedOn: administrator
UserName: Ann
SettingsBasedOn: John
You could put the code to "transfer" settings changes between profiles in a stored procedure or maybe a trigger (might be the best option...) so that when a profile is changed, any other profiles where the SettingsBasedOn equals the UserName also get updated. Might need to add a flag that indicates whether or not the user modified their own settings (so this auto setting update doesn't take place).
The only issue is that the administrator profile will probably need to have their SettingsBasedOn value set to NULL (since that profile is based on nothing). This throws a wrench into SQL Server's referential integrity settings as you can't enforce RI when using a field that can be set to NULL. You can turn off the RI enforcement, but that's leaning away from good DB design. Your call.
July 29, 2005 at 7:42 am
The administrator can be based on her/himself or have a special value, eg zero or negative. It need not be null.
Thinking about it, everyone has a reference set for every attribute, either to themselves (ie customised) or to the appropriate parent.
I don't like triggers but that does give you recursion navigating down the tree. Of course, if the tree ever develops a loop, you hit one of the big risks of triggers(!) so I still would advocate the nested set structure which makes loop impossible (at least with a simple implementation).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply