Software Documentation

Status

8.Jan 2010: Stocktaking at home and office complete, database up-to-date and backup dump generated. All used wine tags entered into database, these stretch back to Barolo trip in Nov 07. Bring transactions up-to-date to make database consistent back to Nov 07 including purchase quantities, unit prices, transaction dates and total costs. All basic functions for wines, producers, stock, tastings, dealers, transactions and purchases are programmed. Updates and deletes are not programmed and must be done using phpMyAdmin (except for adjusting stock levels), this means that any re-entry of key attributes will produce duplicate records although the software should cope with this ok. The workflow is programmed by indicating "next steps" however the step from adding a transaction to adding wines for that transaction still feels disjointed. Next jobs are to add wine tours and bring the transaction details up-to-date.
Known problems:

TBD

Development Environment

Joins

Joins are necessary to allow incomplete records in the database such as wines with unknown producer and transactions with unknown supplier.

"table1 LEFT OUTER JOIN table2 ON condition" couples each record of table1 with its associated record in table2 as qualified by the condition, however any records in table1 without an associated part in table2 are returned with the table2 attributes set to NULL. This allows a complete display of incomplete records.

For example, the following query returns wine records even when the producer is undefined:

"SELECT * FROM wine LEFT OUTER JOIN producer
ON wine.producer_id=producer.producer_id"

Unions

The fact that producers can sell wine aswell as manufacture it and considering that our wine tours result mostly in direct sales with the producer concludes that a union of producer and supplier records into one structure is the most appropriate solution to the problem whereby the supplier name may be in the producer table requiring a switch each time we query for supplier name. At the time of changing to this solution the supplier and producer attributes were identical resulting in complete reuse.

Changing to a union meant chosing one table and deleting the other. The supplier table was deleted as it contained much fewer records than the producer table requiring less re-entry of data. This results in the table name "producer" appearing in all cases where the record could be either a producer or a wine dealer (or private). This hides the implementation of a union so long as extensive work to modify the code to use a more representative name is not undertaken. In short, interpret queries on the producer table to be for either producers or dealers whereby producers are suppliers or manufacturers depending on context.

For example, the following query looks for suppliers of a wine rather than the producer:

"SELECT * FROM purchase,transaction LEFT OUTER JOIN producer
ON transaction.supplier_id=producer.producer_id
WHERE purchase.wine_id='$wine_id'
AND transaction.transaction_id=purchase.transaction_id"

whereas this query looks for manufacturers of wine:

"SELECT * FROM wine LEFT OUTER JOIN producer
ON wine.producer_id=producer.producer_id
WHERE wine.wine_id='$wine_id'"

both queries access the table "producer", this is due to the union.