November 30, 2010 at 8:27 pm
I am working on a project that has a hierarchy. Each level of the hierarchy may have a value, and I want to select the first value up the chain. What is best practice for doing this?
For example:
Hierarchy - Fixed depth
Global (75)
US (85)
Industry (82)
Sub-industry (null)
Company (null)
I want a table(s) design that would easily let me query this and return 82 (in this case). The thing is, my project will have dozens or hundreds of these override values. Do I just create a table for each level of the hierarchy, join them together, and do an IsNull(CValue, SValue, IValue, UValue, GValue) or is there a better way?
December 1, 2010 at 10:41 am
FYI, works better like this:
Hierarchy - Fixed depth
Global (75)
US (85)
Industry (82)
Sub-industry (null)
Company (null)
Jason Whitney (11/30/2010)
I want a table(s) design that would easily let me query this and return 82 (in this case). The thing is, my project will have dozens or hundreds of these override values. Do I just create a table for each level of the hierarchy, join them together, and do an IsNull(CValue, SValue, IValue, UValue, GValue) or is there a better way?
Um, pretty much, yeah. You want COALESCE() instead of ISNULL() (Isnull only takes two values).
I know of another approach but it basically requires you to hold a table like the above, and then the 'final' value is stored alongside your product. Whenever you edit the Category list for the product, you'd then push the final value down to this table. It does, however, mean that you have to deal with a master category table so you only do lookups in one place, as well as make sure you don't trip on your ID values.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 1, 2010 at 5:57 pm
Thanks for your help. I was hoping there was a better way, but coalesce it is!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply