MasterDetail

Overview

The term "master/detail" is a database concept that means that there is a general dataset/table (the master dataset). For each item in the master dataset, zero, one or more detail items are available in a detail dataset.

In relational databases, this is called a 1:N relation and is represented by a foreign key in the detail table to the master table.

Classical examples of master/detail relations are

• customers with orders
• orders with ordered articles

SQLDB implementation

You can use the datasource property in the detail dataset to link to the master dataset.

The approach below applies to both Lazarus and FPC code that uses the built in SQLdb Package database layer. It may also apply to other database layers (e.g. Zeos); please check with their documentation.

Example from the EMPLOYEE database (as used in SQLdb Tutorial0 and other tutorials):

• a CUSTOMER table with an integer primary key CUST_NO and other fields
• a SALES table with a CUST_NO integer field that is a foreign key linking to the CUST_NO field in the CUSTOMER table

On the form:

• use one connection, 1 transaction, but 2 queries, 2 data sources
• a master query called qryCustomers selects from the CUSTOMER table
• a detail query called qrySales that selects from the SALES table

In the detail query qrySales:

• set the database property as usual
• set the datasource property to point to the master datasource
• in the query SQL, use a WHERE query to limit the select; use the detail fieldname and limit it to a parameter with the name of the field in the master table

SELECT * from SALES WHERE SALES.CUST_NO=:CUST_NO

In this case we use the SALES.CUST_NO field that happens to have the same field name as the master field CUST_NO, but that need not be the case. As the master datasource keeps track of the current record for the master query qryCustomers, FPC can see the CUST_NO parameter as a reference to the current value of the CUST_NO field in the master/qryCustomers query.

Make sure that the master query is open before the detail query so it can look up fields.

Adding detail records with the right foreign key

Although detail records scroll with master records, additional code is needed.

When adding new detail records, the SALES.CUST_NO field is still NULL unless we fill it. So we need to set up an AfterInsert event handler for qrySales:

procedure TForm1.qrySalesAfterInsert(DataSet: TDataSet);
begin
  DataSet.FieldByName('CUST_NO').AsInteger := qryCustomers.FieldByName('CUST_NO').AsInteger;
end;

See also

• http://www.freepascal.org/docs-html/fcl/sqldb/usingparams.html Documentation on using parameters, including implementation for master/detail
• http://docwiki.embarcadero.com/RADStudio/XE3/en/Establishing_Master-detail_Relationships_Using_Parameters The documentation for the equivalent Delphi feature
• http://forum.codecall.net/topic/62683-master-detail-datasets/
• Lazarus Tdbf Tutorial#Master table relations Example of using master/detail with the TDBF components
• SQLdb Tutorial0 - Set up instructions for sample data for the subsequent database tutorials. Has master/detail tables