Master/Detail Relationships

Master/detail (MD) relationship between two tables is a very widespread one. So it is very important to provide an easy way for database application developer to work with it. Lets examine how PgDAC implements this feature.

Suppose we have classic MD relationship between "Department" and "Employee" tables.

"Department" table has field Dept_No. Dept_No is a primary key.

"Employee" table has a primary key EmpNo and foreign key Dept_No that binds "Employee" to "Department".

It is necessary to display and edit these tables.

PgDAC provides two ways to bind tables. First code example shows how to bind two TCustomPgDataSet components (TPgQuery, TPgTable, or TPgStoredProc) into MD relationship via parameters.

procedure TForm1.Form1Create(Sender: TObject);
var
  Master, Detail: TPgQuery;
  MasterSource: TDataSource;
begin
  // create master dataset
  Master := TPgQuery.Create(Self);
  Master.SQL.Text := 'SELECT * FROM Department';

  // create detail dataset

  Detail := TPgQuery.Create(Self);
  Detail.SQL.Text := 'SELECT * FROM Employee WHERE Dept_No = :Dept_No';

  // connect detail dataset with master via TDataSource component
  MasterSource := TDataSource.Create(Self);
  MasterSource.DataSet := Master;
  Detail.MasterSource := MasterSource;

  // open master dataset and only then detail dataset
  Master.Open;
  Detail.Open;
end;

Pay attention to one thing: parameter name in detail dataset SQL must be equal to the field name or the alias in the master dataset that is used as foreign key for detail table. After opening detail dataset always holds records with Dept_No field value equal to the one in the current master dataset record.

There is an additional feature: when inserting new records to detail dataset it automatically fills foreign key fields with values taken from master dataset.

Now suppose that detail table "Department" foreign key field is named DepLink but not Dept_No. In such case detail dataset described in above code example will not autofill DepLink field with current "Department".Dept_No value on insert. This issue is solved in second code example.

procedure TForm1.Form1Create(Sender: TObject);
var
  Master, Detail: TPgQuery;
  MasterSource: TDataSource;
begin
  // create master dataset
  Master := TPgQuery.Create(Self);

  Master.SQL.Text := 'SELECT * FROM Department';

  // create detail dataset
  Detail := TPgQuery.Create(Self);
  Detail.SQL.Text := 'SELECT * FROM Employee';

  // setup MD
  Detail.MasterFields := 'Dept_No';   // primary key in Department
  Detail.DetailFields := 'DepLink'; // foreign key in Employee

  // connect detail dataset with master via TDataSource component
  MasterSource := TDataSource.Create(Self);
  MasterSource.DataSet := Master;

  Detail.MasterSource := MasterSource;

  // open master dataset and only then detail dataset
  Master.Open;
  Detail.Open;
end;

In this code example MD relationship is set up using MasterFields and DetailFields properties. Also note that there are no WHERE clause in detail dataset SQL.

To defer refreshing of detail dataset while master dataset navigation you can use DetailDelay option.

Such MD relationship can be local and remote, depending on the TCustomDADataSet.Options.LocalMasterDetail option. If this option is set to True, dataset uses local filtering for establishing master-detail relationship and does not refer to the server. Otherwise detail dataset performs query each time when record is selected in master dataset. Using local MD relationship can reduce server calls number and save server resources. It can be useful for slow connection. CachedUpdates mode can be used for detail dataset only for local MD relationship. Using local MD relationship is not recommended when detail table contains too many rows, because in remote MD relationship only records that correspond to the current record in master dataset are fetched. So, this can decrease network traffic in some cases.

Master/Detail Relationships

See Also