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 ***
