MasterDetail

Overview

In relational databases the master/detail concept is also called 1:N or one-to-many. It means that one record in a table/dataset, can be related to many records in another table/dataset. It is a model that uses data from one dataset to filter data in another dataset. The dataset that is used to filter the data is called Master and the dataset that is filtered is called Detail.

For example, countries have many cities. If you select a country and want to see all the cities, thats a good job for Master/Detail. The countries table could be master, and cities table could be detail.

The filtering works because the detail table contains an identifier from the master table, usually the master tables primary key. So this key exists in both the master and detail table. In the detail table it is called a foreign key.

Classical examples of master/detail relations are

• customers with orders
• orders with ordered articles

In our example below we'll work with a customer table and an order table.

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