"ventre-à-pattes" <ne**@ventre-a-pattes.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.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.
