Sparse Hierarchy T-SQL Help...PLEASE?

[Darryll Petrancuri]

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.CustomerID, 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.TargetID
AND _Rate.OwnerLevel = Filtered_Rate.OwnerLevel

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

[--CELKO--]

>> I could really use some suggestions on how to improve on the
following, it at all possible: <<

I just wrote a book on TREES & HIERARCHIES IN SQL (look at
Amazon.com). This might help.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. I gave up trying to read it. It looks like you are
using GUIDs for keys in violation of all relational design principles,
that data elements are changing names from table to table and you have
a vague universal, meaningless attribute called "id" floating
everywhere.

Would please post something that makes sense? Nobody wants to figure
out your DDL, insert the data and then test the code for you for free.
And you do not want to pay my rates :)

[Joe Celko]

This is some stuff we did off-line, but I like to post to newsgroups:

Please read ISO-11179 for the rules for naming data elements. Tables
should be plural, class or collective nouns. Keys by definition should
not be exposed physical locators, like GUIDs, IDENTITY, autoincrement,
ROWID and all those other proprietary things based on the state of the
computer hardware at some point in time or the layout of a hard drive.
They are a subset of attributes of the entity being modeled in a table.
Look at anything in the Universe; there is no GUID on it; God uses a
17-letter Hebrew word that only special rabbis can see :)

Data element names do not change from table to table. Keys are not
NULL-able. There is no magic, universal "id" in the relational model.
That was record numbers in sequential files in the 1950's.

I am assuming that customers show up a facility and get billed a dollar
amount rate. I assume that the facility_rate is between cust_rate and
global_rate.

Start by modeling entities; sin e we can see and touch these two things,
they are probably entities:

CREATE TABLE Customers
(customer_id INTEGER NOT NULL PRIMARY KEY,
..);

CREATE TABLE Facilities
(facility_id INTEGER NOT NULL PRIMARY KEY,
..);

Now show the relationship between these two entities. I picked a hotel
term.

CREATE TABLE Occupancies
(facility_id INTEGER NOT NULL PRIMARY KEY,
REFERENCES Facilities(facility_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
customer_id INTEGER NOT NULL
REFERENCES Customers (customer_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (facility_id, customer_id),
global_rate DECIMAL(12,4) NOT NULL,
facility_rate DECIMAL(12,4),
cust_rate DECIMAL(12,4),
CHECK (facility_rate < global_rate),
CHECK (cust_rate < facility_rate),
CHECK (CASE WHEN cust_rate IS NOT NULL
AND facility_rate IS NULL
THEN 0 ELSE 1 END = 1),

..);

You can re-write the last constraint with some COALESCE() expressions,
but the CASE is clearer to read. It forces a facility or global rate to
exist when there is no customer rate. Likewise if the facility rate
does not exist, the Global rate always exists.

Your query is now simply this:

SELECT facility_id, customer_id,
COALESCE (cust_rate, facility_rate, global_rate) AS rate
FROM Occupancies;
If you want to raise the rates on someone, use an explicit column
instead of the COALESCE(). Most hard queries are the result of errors
in the DDL.

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!