Section 11.4. Superseding a General Case


11.4. Superseding a General Case

The technique of hiding a sort key within a query in the from clause, which I used in the previous section to display bands, can also be helpful in other situations. A particularly important case is when a table contains the definition of a general rule that happens to be superseded from time to time by a particular case defined in another table. I'll illustrate by example.

I mentioned in Chapter 1 that the handling of various addresses is a difficult issue. Let's take the case of an online retailer, one that knows at most two addresses for each customer: a billing address and a shipping address. In most cases, the two addresses are the same. The retailer has decided to store the mandatory billing address in the customers table and to associate the customer_id identifier with the various components of the address (line_1, line_2, city, state, postal_code, country) in a different shipping_addresses table for those few customers for whom the two addresses differ.

The wrong way to get the shipping address when you know the customer identifier is to execute two queries:

  1. Look for a row in shipping_addresses.

  2. If nothing is found, then query customers.

An alternate way to approach this problem is to apply an outer join on shipping_addresses and customers. You will then get two addresses, one of which will in most cases be a suite of null values. Either you check programmatically if you indeed have a valid shipping address, which is a bad solution, or you might imagine using the coalesce( ) function that returns its first non-null argument:

 select ... coalesce(shipping_address.line_1, customers.line_1), ... 

Such a use of coalesce( ) would be a very dangerous idea, because it implicitly assumes that all addresses have exactly the same number of non-null components. If you suppose that you do indeed have a different shipping address, but that its line_2 component is null while the line_2 component of the billing address is not, you may end up with a resulting invalid address that borrows components from both the shipping and billing addresses. A correct approach is to use case to check for a mandatory component from the addresswhich admittedly can result in a somewhat difficult to read query. An even better solution is probably to use the "hidden sort key" technique, combined with a limit on the number of rows returned (select top 1..., limit 1, where rownum = 1 or similar, depending on the DBMS) and write the query as follows:

 select * from (select 1 as sortkey,              line_1,              line_2,              city,              state,              postal_code,              country       from shipping_addresses       where customer_id = ?       union       select 2 as sortkey,              line_1,              line_2,              city,              state,              postal_code,              country       from customers       where customer_id = ?       order by 1) actual_shipping_address limit 1 

The basic idea is to use the sort key as a preference indicator. The limit set on the number of rows returned will therefore ensure that we'll always get the "best match" (note that similar ideas can be applied to several rows when a row_number( ) OLAP function is available). This approach greatly simplifies processing on the application program side, since what is retrieved from the DBMS is "certified correct" data.

The technique I've just described can also be used in multilanguage applications where not everything has been translated into all languages. When you need to fetch a message, you can define a default language and be assured that you will always get at least some message, thus removing the need for additional coding on the application side.




The Art of SQL
The Art of SQL
ISBN: 0596008945
EAN: 2147483647
Year: N/A
Pages: 143

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net