Relationships between tables

We will use the tables declared in ooftst02.h as example tables for this page.

General relationships

A relationship between tables means that there is some correspondance between records of the two tables, for example a surgery may have a table of the medical staff that works there and another table of the patients that have come in. The relationship would be formed between the patients and the staff that attended them. In this case the relationship might take the form:

There are three different types of relationship. These are based on whether there is only one or several records on each side of the relationship. The relationship types are:

• A one to one (1:1) relationship where each record in one table has a single corresponding record in the other.
• A many to many (N:M) relationship where each record in each table may have more or less than one corresponding record in the other table
• A one to many (1:N) relationship where each record in one table may have more or less than one corresponding record in the other table. The records in the other table may only have one corresponding record from this table.

For example, consider possible relationships between databases dbPeople and dbCompanies (assuming no moonlighting on a second job!).

A limitiation of the current M:N relationships is that you must declare an intermediate table to do the mapping. Thus, while we can navigate People.buysFrom->CompanyName, we actually have to declare a Purchases database, that sits in between:

Defining the relationship

1 - Declaring the traversal paths

Relationships in OOFILE, like other OODBMS, are managed by "traversal paths" between the related  tables.

OOFILE traversal paths are declared with two macros, a Ref and  Set. The difference is that a Ref is used for a one-to-one or "1-ary" and a Set is used in an "n-ary" relationship.

A traditional M:N relationship is therefore declared with a Set ("n-ary") relationship on each side.

We must declare this by calling one of either DECLARE_REF or DECLARE_SET for each table.

eg:
DECLARE_REF(dbPatients)
DECLARE_SET(dbVisits)

Then in the actual class, we must give a field (or field set) that will be the reference to the other table.

eg:
DECLARE_CLASS(dbPatients) {

...

dbVisitsSet Visits;

...

};

DECLARE_CLASS(dbVisits) {

...

dbPatientsRef Patient;

...

};

2 - Defining the concrete relationship

A link is defined between two concrete dbTable classes representing real tables (not the table class definitions). We must specifically declare the relationship between the fields (declared above).

eg:
dbRelationship PatientVisits(People.Visits, Visits.Person);

Note on names

There is no restriction on naming, the use of Visits as a table object is purely a convention for the table dbVisits. Similarly the traversal path People.Visits can be named anything you like.

Table expressions through related fields

A related field expression starts with a traversal path and involves the normal pointer operator->. This ends in a field and may go through intermediate traversal paths eg:
People.Visits->VisitDate                  -   1 link away
People.Visits->Doctor->Name       -  2 links away

Each operator-> along the path creates a link in a temporary chain. The operator returns a real dbTable* that points to a cloned table. The cloned table manages the related selection.

The same mechanism is used when operations are called on the related tables, eg:
People.Visits->count();

Loading related values

Loading the related context is delayed until needed (known as lazy implementation). This could be prompted by a related table command (count, newRecord, start...) or accessing a related field.

Relationship chains are marked as valid by the starting point - if you change its context then the current chains are invalidated.

When a related value must be loaded, the chain may already be valid - implying a valid table at the end of the chain. This means the field just loads its value as normal.

If the chain is invalid, the links in the chain are followed from the left, loading the related context for each linked table. eg
People.Visits->Doctor->Name
loads a related context in Visits and again in Doctor.

This loading related context is independent of *how* the relationship is stored - it may be a runtime join over key fields, be stored pointers, physically aggregated records or some other mechanism.

Parent-Child Relationships

Relationships are considered Parent-Child when you add the related records through the Parent on the LHS. More general linking (closer to the ODMG standard) is described below. New and changed records on the RHS of relationships are saved automatically. However, if you don't want deletes to be propagated, you can explicitly call propagateRelatedDeletes() on the LHS of the expression, eg: in the constructor:

DECLARE_CLASS(dbPatients)

dbChar LastName, Othernames;

dbVisitsSet Visits;

dbLong Salary;

dbPatients() :

LastName(40, "Last Name", kIndexed),

Othernames(80, "Other names", kIndexed),

Salary("Salary", kIndexed)

{

Visits.propagateRelatedDeletes(false);

};

};

******* WARNING *******

If you use a pointer-based (non-join) relationship, the LHS table may be saved more than once. This could affect you if you override the dbTable::saveRecord() with one of your own, with side-effects. Normally it is not a problem.

The reason is for 1-1 relationships - the LHS table is saved first to get its record pointer, then the RHS table is saved, which returns its record pointer, then the LHS table must be saved again with the pointer to the RHS.

This behaviour will be optimised in future versions to avoid redundant saves. However, there will still be times when the LHS record is saved twice (if both are new records).

Feature index

(c) Copyright A.D. Software 1994-2000 (All Rights Reserved).
Last Updated: 9th September 2001