Greetings!
I could really use some suggestions on how to improve on the
following, it at all possible:
Table 'Customer'
---------------------
ID GUID PK
....
Table 'Facility'
-----------------
ID GUID PK
CustomerID GUID FK (FK to Customer GUID)
....
Table 'Rate'
----------------
ID PK
OwnerID GUID Nullable FK (FK to Customer, Facility GUID PK)
OwnerLevel INT Contraint 1-3
<Rate Data>
Table 'Rate' is a sparse hierarchy of data. There are 3 possible
levels in the hierarchy as follows:
OwnerID <NULL>
OwnerLevel 1
This indicates Global rate data.
OwnerID <Customer.ID>
OwnerLevel 2
This indicates Customer-specific rate data.
OwnerID <Facility.ID>
OwnerLevel 3
This indicates Facility-specific rate data.
Now, a given Customer need not have an entry in the Rate table. If a
Customer does not have an entry, it is supposed to 'inherit' Global
rate data. A given Facility need not have an entry in the Rate table.
If a Facility does not have an entry, it is supposed to inherit
Customer-specific rate data, and in the absence of an entry for the
Facility's parent Customer, it is supposed to inherit Global rate
data.
The challenge is that I want to write a view to give me back the
appropriate rate record for Customer and Facility. Here's what I've
done so far.
View _Rate
--------------
SELECT
Rate.*,
NULL AS TargetID
FROM
Rate
WHERE
Rate.OwnerID IS NULL
UNION
SELECT
Rate.*,
Customer.ID AS TargetID
FROM
Rate
CROSS JOIN
Customer
WHERE
Rate.OwnerID IS NULL
OR Rate.OwnerID = Customer.ID
UNION
SELECT
Rate.*,
Facility.ID AS TargetID
FROM
Rate
CROSS JOIN
Facility
WHERE
Rate.OwnerID IS NULL
OR Rate.OwnerID IN (Facility.Custo merID, Facility.ID)
View view_Rate
--------------------
SELECT
_Rate.*
FROM
_Rate
INNER JOIN
(
SELECT
TargetID,
MAX(OwnerLevel) AS OwnerLevel
FROM
_Rate
GROUP BY
TargetID
) AS Filtered_Rate
ON
_Rate.TargetID = Filtered_Rate.T argetID
AND _Rate.OwnerLeve l = Filtered_Rate.O wnerLevel
The combination of these two views gives a resultset that contains 1
record for every Target ID as follows:
TargetID <NULL>
OwnerID <NULL>
OwnerLevel 1
This indicates Global rate data established at the Global level.
TargetID <Customer.ID>
OwnerID <NULL>
OwnerLevel 1
This indicates Customer rate data for the specific Customer identified
by Customer.ID is inherited from the Global rate data.
TargetID <Customer.ID>
OwnerID <Customer.ID>
OwnerLevel 2
This indicates Customer-specific rate data for the specific Customer
identified by Customer.ID (not inherited).
TargetID <Facility.ID>
OwnerID <NULL>
OwnerLevel 3
This indicates Facility rate data is inherited from the Global rate
data.
TargetID <Facility.ID>
OwnerID <Customer.ID>
OwnerLevel 2
This indicates Facility rate data for the specific Facility identified
by Facility.ID is inherited from the Facility's parent Customer's
Customer-specific rate data.
TargetID <Facility.ID>
OwnerID <Facility.ID>
OwnerLevel 3
This indicates Facility-specific rate data for the specific Facility
identified by Facility.ID (not inherited).
I know this is a lengthy post, and a complicted query scenario, but
I'm not willing to accept that my solution is the best solution just
yet. Please consider that I really need this functionality in a VIEW
as much as possible.
Thank you for your learned consideration.
I eagerly await your replies.
Darryll