Alternate values

I have no idea where to look for this.

Database about wines.
One table is `product` (one particular wine from one particular estate)
with a link to a second table `appellation` (legal classification).
Another table (say `bottle`) links to one `product` and has additional
fields, most notably the vintage.

Now one problem is that some years, the same product might be refused
the usual category (decision of a tasting commission).
Another possibility is that the appellation might change at some point
in time (creation of a new category, or upgrade or downgrade).
And finally, the name of the product itself might change for some
year(s) (e.g. Cuv=E9e Tchernobyl for the 1986 vintage).

Still, in all these cases, I want to look at all `bottle` items as one
single product, but display the correct appellation. And I want the
link to `appellation` to be at the `product` level, not `bottle`.

How can I manage to get the right category when I create a new `bottle`
item for an existing product? Or should I just forget about it?

Thanks in advance
--=20
Eric Lafontaine

Re: Alternate values

"ventre-à-pattes" wrote in message
news:1139457145.328652.146980@g47g2000cwa.googlegroups.com.. .
> One table is `product` (one particular wine from one particular estate)
> with a link to a second table `appellation` (legal classification).
> Another table (say `bottle`) links to one `product` and has additional
> fields, most notably the vintage.
[description of exception cases]

Well, one solution would be to introduce a new table in between bottle and
product. Maybe call it `product_year` or something. This table would store
multiple rows that each reference a given product, and differ in the year
and in the other attributes that may vary per year, like the appellation and
the product name. The problem with this solution is that there's a lot of
duplication, because I assume the exceptions occur more or less rarely.

A different solution would be to create a new table similar to the one
above, but store in it only records for the exception cases. Most years
there would be no record in this new table. Perhaps call it
`product_exception`.

You can use an outer join and the COALESCE function to apply these
exceptions to override the value in the product table. COALESCE returns the
first non-NULL argument, so in cases where there is no matching record in
the exception table, it uses the default product name and appellation in the
`product` table.

SELECT b.year, COALESCE(pe.product_name, p.product_name),
COALESCE(pe.product_appellation, p.product_appellation),
FROM bottle AS b
INNER JOIN product AS p
ON b.product_id = p.product_id
LEFT OUTER JOIN product_exception AS pe
ON b.year = pe.year AND p.product_id = pe.product_id

Regards,
Bill K.

Re: Alternate values

Bill Karwin wrote:
[...]
> You can use an outer join and the COALESCE function to apply these
> exceptions to override the value in the product table. COALESCE returns the
> first non-NULL argument, so in cases where there is no matching record in
> the exception table, it uses the default product name and appellation in the
> `product` table.

Thanks for the pointer, I'll give it a try
--
Eric Lafontaine

Re: Alternate values

Bill Karwin wrote:
[...]

Since you have been so kind...

> ...COALESCE...

This seems to work fine for individual exceptions.
Now how can I proceed for the following case: SomeWine belonged to
appellation Old until some date, and then changed to appellation New.
I am much more likely to purchase recent vintages, so New would be my
default.

Is there any way to specify a range of vintages without having
somewhere a record for each of them? And of course I'd like it to be in
the database itself...
--
Eric Lafontaine

Re: Alternate values

"ventre-à-pattes" wrote in message
news:1139466138.932249.186560@g44g2000cwa.googlegroups.com.. .
> Now how can I proceed for the following case: SomeWine belonged to
> appellation Old until some date, and then changed to appellation New.
> I am much more likely to purchase recent vintages, so New would be my
> default.
>
> Is there any way to specify a range of vintages without having
> somewhere a record for each of them? And of course I'd like it to be in
> the database itself...

This is getting to be more complex. One could change the product_exception
table I described before by listing a year_start and a year_end, and then do
your queries as follows:

SELECT b.year, COALESCE(pe.product_name, p.product_name),
COALESCE(pe.product_appellation, p.product_appellation)
FROM bottle AS b
INNER JOIN product AS p
ON b.product_id = p.product_id
LEFT OUTER JOIN product_exception AS pe
ON (b.year BETWEEN pe.year_start AND pe.year_end) AND p.product_id =
pe.product_id

But what to list for year_end if the product has changed? Maybe move the
old default values into a record in the exception table, because for those,
we do know an end date. Then change the default values in the product
table.

But this is not a comfortable design. If another programmer comes to
maintain the system two years from now, he or she will be confused.

We might as well take the changeable properties out of the product table
altogether, and force _all_ product names and appellations to be looked up
in the second table. This table is no longer for rare exceptions, it stores
year-dependent attributes for all years.

SELECT b.year, py.product_name, py.product_appellation
FROM bottle AS b
INNER JOIN product AS p
ON b.product_id = p.product_id
INNER JOIN product_year_attribute AS py
ON (b.year BETWEEN py.year_start AND py.year_end
OR b.year >= py.year_start AND py.year_end IS NULL)
AND p.product_id = py.product_id

I have an additional join condition term in there, to support a state where
year_end is NULL. This indicates the state where the end date is not known,
i.e. it's the current and ongoing name and appellation for the wine.

Another caveat: there's no way in this design for the database to enforce
that there are no overlaps. If you put in a couple of records with
overlapping periods, you'll get multiple records back. If you have a year
with an exception case, you'd have to split the record for the default
attributes into two, one ending before the exception year, and the second
beginning after the exception year.

You could also combine this structure with the exception-case table and
COALESCE that we discussed before. That would allow you to have single-year
exceptions without having to split long durations of "default" attributes
into multiple records.

SELECT b.year, COALESCE(pe.product_name, py.product_name),
COALESCE(pe.product_appellation, py.product_appellation)
FROM bottle AS b
INNER JOIN product AS p
ON b.product_id = p.product_id
INNER JOIN product_year_attribute AS py
ON (b.year BETWEEN py.year_start AND py.year_end
OR b.year >= py.year_start AND py.year_end IS NULL)
AND p.product_id = py.product_id
LEFT OUTER JOIN product_exception AS pe
ON b.year = pe.year AND p.product_id = pe.product_id

One thing I've learned about modelling real-world industries with software:
in the real world, exception cases are common, and they're not very
expensive to track. Humans are pretty good at visualizing exception cases.
In software, exception cases are bothersome because they necessarily force
complexity into _all_ your computations.

Regards,
Bill K.

Re: Alternate values

Bill Karwin wrote:
> This is getting to be more complex.
[...]
> One thing I've learned about modelling real-world industries with software:
> in the real world, exception cases are common, and they're not very
> expensive to track. Humans are pretty good at visualizing exception cases.
> In software, exception cases are bothersome because they necessarily force
> complexity into _all_ your computations.

So maybe I will try to stick to the KISS rule and just add one
`exception` field in my `bottle` table that would be null by default,
and use COALESCE.
Thanks again for making me aware of that function.
--
Eric Lafontaine