Extending the Invoices Example

In this section we will change the database definition to make it more correct in the relational sense. In a relational database each field in a table should be a fact about the primary key (this is a simple summary of 'Boyce-Codd Normal Form' or 'BCNF'). In the first version of the Invoices database we had a table for the invoices whose fields were indeed facts about the primary key. The primary key was 'Invoice ID' and the three other fields were facts about that key. For this reason we can leave the the Invoice table unchanged (be sure to use Short Date for the Date format).

Note the date format is set to Short Date.

We need to make some changes, however, to the second table. In the first version we included some facts about the Invoice Item but we also included some facts about the Item itself, in particular its unit cost and a description. These are not facts about the primary key, they are not directly related to a line on an invoice and they should be placed in a separate table. This leaves us with the following table definition for the Invoice Items:

For each invoice item we include its ID, the invoice on which it appears, the quantity ordered and the total cost. Further information about the item is stored in a new table:

Note how we now naturally add the Stock item to the table, in the previous version it clearly did not fit with the invoice item. The relationships between these three tables now look like this:

An invoice is a list of items, each of which has properties that are not directly related to the invoice on which they appear. This is a common pattern in relational databases, a central item with two related tables and one-to-many relationships between them. This arises because relationships are often many-to-many but need to be broken down into two one-to-many relationships to fit into the relational database model. Other examples would be a library borrowing system where a 'loan' links many borrowers and many books, and a school database where there must be a link between the many pupils and the many timetable options. In this example we have created a primary key from an AutoNumber called 'Invoice Item', but we could have used the key pair Item ID-Invoice as the unique identifier (thus disallowing the possibility of an item appearing twice on one invoice). In this case we can see even more clearly that only Quantity and Total Cost are fully dependent on the primary key pair and that the Description, the Unit Cost and the Stock level are dependent only the Item ID, they have no direct connection with an Invoice.

Constructing the Form in Delphi

Now we can set up a form in Delphi to display the Invoice table and the Invoice Items in a grid below it, just as we did in the previous section. Set the MasterSource of ADOTable2 to ADOTable1 and set the MasterField to the Invoice ID so that the items are displayed for each invoice.

Add the Item Details table in a grid underneath the Invoice Items. We cannot link this to the two other tables because we have already used the MasterSource and MasterField properties of ADOTable2 but we can display it so users can see the items they are adding to the Invoice and we can use its related data in reports.

Picking up the information about the unit cost of each invoice item is slightly more complicated than before because this is now stored in Table3. To retrieve the Unit Cost information for the Invoice Item we need to compare the ItemID entered by the user with those in the Item Details table and pick up the UnitCost when a match is found. As we are accessing the Item Details table we can also update the stock value by subtracting the current order quantity from the value in the table. The procedure will thus do the following:

• set a variable to hold the Unit Cost to zero
• set a variable to hold the current value of Item ID
• find the value of the Unit Cost in the Item Details table
• calculate the value of the total cost of an item as the retrieved Unit Cost * the Quantity ordered

procedure TForm1.ADOTable2QuantityChange(Sender: TField);
//Match current ItemID in Table2 with corresponding ItemID in Table3;
// update Stock value in Table3 and TotalCost in Table2
var unitcost:real;
     item, stock:integer;
begin
 ADOTable3.DisableControls;
 try
  unitcost:=0.0;
  stock:=0;
//set variable to current ItemID
  item:=ADOTable2ItemID.Value;
  ADOTable3.First;
  while not ADOTable3.Eof do
  begin
//compare item variable with corresponding item in Table3
   if ADOTable3ItemID.Value = item then
   begin
//read current stock level
    stock := ADOTable3Stock.Value;
    with ADOTable3 do
    begin
//update Stock field in Table2
     Edit;
     FieldValues['Stock'] := stock - ADOTable2Quantity.Value;
     Post;
     Refresh;
    end;
//read UnitCost field from Table3 while we are still at the right point
   unitcost:=ADOTable3UnitCost.Value;
  end;
//get next record
 ADOTable3.Next;
 end;
 finally
  ADOTable3.EnableControls;
 end;
//set total cost field to retrieved value * quantity
 ADOTable2TotalCost.Value:=unitcost * ADOTable2Quantity.Value;
end;

To update the TotalCost field in the Invoices table we use a separate piece of code attached to a DBNavigator click and a FormClose event:

procedure TForm1.DBNavigator2Click(Sender: TObject; Button: TNavigateBtn);
//calculate total value of items in current Table2 dataset, post to Table1.TotalCost
var totalvalue:real;
begin
 ADOTable2.DisableControls
 try
  totalvalue:=0;
  ADOTable2.First;
  while not ADOTable2.Eof do
  begin
   totalvalue := totalvalue+ADOTable2TotalCost.value;
   ADOTable2.Next;
  end;
  with ADOTable1 do
  begin
   Edit;
   FieldValues['TotalCost'] := totalvalue;
   Post;
   Refresh;
  end;
 finally
  ADOTable2.enablecontrols;
 end;
end;

This procedure performs the following actions:

• calculates the total value of an invoice by adding up the Total Cost of each item in an invoice
• updates the Table 1 Total Cost field from the total cost of all the items in an invoice

This is similar to the procedure we used in section 2 to update the Total Cost field.

Database Home