From the record point of view, and to follow the ORM vocabulary (in Ruby on Rails, python, or other ActiveRecord clones), we could speak of "has many" relationships.

In fact, a separate pivot table is created in the database, containing two references to both related records. 

Additional information can be stored in this pivot table. It could be used, for instance, to store association time or corresponding permissions of the relationship. This is called a "has many through" relationship. 

Martin Fowler defines the Active Record pattern as:
An object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic on that data.
In short, this is the ORM approach. 

In our framework, we started from the record point of view.
Since the "has many through" relationships will need one record with optional additional fields, we'll start from this pivot table.
A dedicated class, inheriting from the standard TSQLRecord class (which is the base of all objects stored in our ORM), has been created, named TSQLRecordMany.

The implementation is therefore the following:
  • this TSQLRecordMany class must be named after an existing TSQLRecord class, for instance TSQLDests for storing TSQLDest - this is called the "Dest" record, in our terminology (this naming convention is mandatory, because the ORM engine uses this to guess the "Dest" record type);
  • the TSQLRecord which will contain this TSQLRecordMany as published property will be called the "Source" record, in our terminology;
  • this TSQLRecordMany published property will be initialized by TSQLRecord.Create, and will make available direct access to the pivot table, without any need to additional code, via the DestAdd/FillMany/SourceGet/DestGet methods;
  • both "Source" and "Dest" records are refered by a TRecordReference field in the TSQLRecordMany table columns: therefore a single TSQLRecordMany class/Table can be used by several "Source" classes/tables, that is the same TSQLRecordMany type can be used in several classes as published properties.

First of all, the DestAdd method is used to create a relationship, that is to add a row to the pivot table.
Source and Dest IDs are to be provided to this method, and additional "through" fields are taken from the current values of the TSQLRecordMany instance.

Most of the time, in case of a pure "has many" relationship, only the Dest IDs are searched, according to a specified Source ID.
This is the purpose of the DestGet method, which will return a TIntegerDynArray containing all Dest IDs.

You can also search for the opposite, that is all Source IDs related to a specified Dest ID.
This is the purpose of the SourceGet method, which will return a TIntegerDynArray containing all Source IDs.

In order to handle easily, and in a pure ORM approach, a "has many through" relationship, the FillMany method is to be called, according to a specified Source ID.
It will fill the internal result table of the TSQLRecordMany instance with all "through" pivot table records corresponding to this Source ID.
Therefore, the FillOne method can be used to browse all the matching record of the pivot table, and retrieve all associated column values (association time and such). This implements an easy iterator-based mechanism, without the overhead of creating TCollection instance or such.

You can have additional information (including source code and sample classes) in our forum.
Feedback is welcome!