"has many" and "has many through" relationships
By A.Bouchez on 2010, Thursday October 28, 20:37 - SQLite3 Framework - Permalink
Extracted from wikipedia, here is a definition of 'many to many' relationships in regular database management:
In our SQLite3 framework, we just created a dedicated class for handling such "many to many" relationships.In systems analysis, a many-to-many relationship is a type of cardinality that refers to the relationship between two entities (see also Entity-Relationship Model) A and B in which A may contain a parent row for which there are many children in B and vice versa. For instance, think of A as Authors, and B as Books. An Author can write several Books, and a Book can be written by several Authors. Because most database management systems only support one-to-many relationships, it is necessary to implement such relationships physically via a third and fourth junction table, say, AB with two one-to-many relationships A -> AB and B -> AB. In this case the logical primary key for AB is formed from the two foreign keys (i.e. copies of the primary keys of A and B).
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.
- 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.
See http://synopse.info/forum/viewtopic.php?id=142
Feedback is welcome!