[SQL] View to distill out the most specific rule for a configuration table using production elements

Okay, imagine a configuration table, LTR_UCB_FEE_TKT_BEN_OVERRIDE which has:

id

type (fee, ticket, benevolent donation, only interested in the latter two for this)

fee_no (only for fees)

title_no

prod_no

prod_season_no

perf_no

price_type

[a bunch of columns with data for each configuration]

Only one production element level and price type pairing can be specified on a row (null okay for price type).  Basically if I had a configuration for a specific price type at the title level, I'd want to be able to specify another configuration set at, say, production season with "null" in the price type that would apply to all other price types for the relevant performances, and vice versa.

with pover as (
	select
		ov.type, p.perf_no, ov.price_type, ov.id
	from LTR_UCB_FEE_TKT_BEN_OVERRIDE as ov
		inner join T_PERF as p on p.perf_no = ov.perf_no
), psover as (
	select
		ov.type, p.perf_no, ov.price_type, ov.id
	from LTR_UCB_FEE_TKT_BEN_OVERRIDE as ov
		inner join T_PERF as p on p.prod_season_no = ov.prod_season_no
		left outer join pover as p1 on p1.perf_no = p.perf_no
			and p1.type = ov.type
			and (
				((ov.price_type is null) and (p1.price_type is null))
				or (ov.price_type = p1.price_type)
			)
	where p1.perf_no is null
), prover as (
	select
		ov.type, p.perf_no, ov.price_type, ov.id
	from LTR_UCB_FEE_TKT_BEN_OVERRIDE as ov
		inner join T_PROD_SEASON as ps on ps.prod_no = ov.prod_no
		inner join T_PERF as p on p.prod_season_no = ps.prod_season_no
		left outer join pover as p1 on p1.perf_no = p.perf_no
			and p1.type = ov.type
			and (
				((ov.price_type is null) and (p1.price_type is null))
				or (ov.price_type = p1.price_type)
			)
		left outer join psover as ps1 on ps1.perf_no = p.perf_no
			and ps1.type = ov.type
			and (
				((ov.price_type is null) and (ps1.price_type is null))
				or (ov.price_type = ps1.price_type)
			)
	where p1.perf_no is null
		and ps1.perf_no is null
), tover as (
	select
		ov.type, p.perf_no, ov.price_type, ov.id
	from LTR_UCB_FEE_TKT_BEN_OVERRIDE as ov
		inner join T_PRODUCTION as pr on pr.title_no = ov.title_no
		inner join T_PROD_SEASON as ps on ps.prod_no = pr.prod_no
		inner join T_PERF as p on p.prod_season_no = ps.prod_season_no
		left outer join pover as p1 on p1.perf_no = p.perf_no
			and p1.type = ov.type
			and (
				((ov.price_type is null) and (p1.price_type is null))
				or (ov.price_type = p1.price_type)
			)
		left outer join psover as ps1 on ps1.perf_no = p.perf_no
			and ps1.type = ov.type
			and (
				((ov.price_type is null) and (ps1.price_type is null))
				or (ov.price_type = ps1.price_type)
			)
		left outer join prover as pr1 on pr1.perf_no = p.perf_no
			and pr1.type = ov.type
			and (
				((ov.price_type is null) and (pr1.price_type is null))
				or (ov.price_type = pr1.price_type)
			)
	where p1.perf_no is null
		and ps1.perf_no is null
		and pr1.perf_no is null
)
select 'pover' as level, * from pover
union all
select 'psover' as level, ps.* from psover as ps
union all
select 'prover' as level, pr.* from prover as pr
union all
select 'tover' as level, t.* from tover as t

I am thinking now whether I would want instead to just do a bunch of left joins and use COALESCE to choose the id row in the configuration table.  I was just going to that back in to get the configuration columns, instead of having to spell them out in each row.

At the risk of adding too many layers here, is your concern table complexity or performance when used?  From what I have heard thus far, your primary concern seems to have been performance.  That being said...

Since this is a custom configuration type thing, this might be a good argument for a comprehensive trigger which saves data to a sort of "currently valid configuration table", on which you then write your view.  That would allow the "work" of all the comprehensive COALESCing and LEFT JOINing and all that fun stuff to be completed at the time of saving, which would allow things like the T_PERF, T_PROD_SEASON, etc... tables to be able to be left alone during time of use so as not to tie up the database and hamper performance since your LTX_CURRENT_PERF_CONFIG would have the results of all that work already saved.  Sure, that table would be being updated regularly based on someone saving information to Titles, Productions, etc... but again, with all the "hard work" already out of the way, that would simplify your performance runtime.

Just a(nother) thought.

At the risk of adding too many layers here, is your concern table complexity or performance when used?  From what I have heard thus far, your primary concern seems to have been performance.  That being said...

Since this is a custom configuration type thing, this might be a good argument for a comprehensive trigger which saves data to a sort of "currently valid configuration table", on which you then write your view.  That would allow the "work" of all the comprehensive COALESCing and LEFT JOINing and all that fun stuff to be completed at the time of saving, which would allow things like the T_PERF, T_PROD_SEASON, etc... tables to be able to be left alone during time of use so as not to tie up the database and hamper performance since your LTX_CURRENT_PERF_CONFIG would have the results of all that work already saved.  Sure, that table would be being updated regularly based on someone saving information to Titles, Productions, etc... but again, with all the "hard work" already out of the way, that would simplify your performance runtime.

Just a(nother) thought.

That is a thought.  Performance is definitely my main concern, especially as this ("Override" is the clue) is supposed to be a rarely invoked part of a larger mapping structure, so this will simply embed into a much larger query.  For the moment, however, it's completing in milliseconds, so I think I can stick with my CTE solution for now.