Ok, Let's recap:

• Relational database (SQL) tables are records. We can view them (in our heads) as record type definitions.
• JOINS define (some kind of) derived union types.
• SQL CROSS JOINS are full cartesian products, i.e. union types where no name folding/aliasing is done by the imaginary type system

How can we model that in php?

Classes (and objects) are records. Arrays are records too. Both are candidates for doing the job. We need to be able to somehow represent this (meta) type information and manipulate it. The aim is to have a natural feeling/looking abstraction of the database in php. It should be flexible enough, for us to modify the relations at runtime, as we need. It should allow us in the long run to have a near optimal speed and not too much complications in the end code. That is a tough cookie.

pre 1: table A is - A::{ a, b, c}
pre 2: table B is - B::{ b, c, d}
case 1: view C is - C::{ A.a, A.b, A.c, B.a, B.b, B.c } , i.e. a cross join of A & B
case 2: view D is - D::{ A.a, A.b, A.c, B.c | A.b = B.b }, same as above with a guard

Let's use the above cases as basis for reasoning about building our php models. We can extend them with extras later.

table as active record


class A_Record {
var a,b,c ;
.....
}
function load_A {
$result = db_query('SELECT * from A');
while( $a[] = new( db_get( $result) );
return $a;
}


Skipping most of the details, the above code outlines the basic idea of aquiring/loading into memory all values of type A, make sure it is not an endless loop, the real life implementation of the database abstraction layer you are using. The methods of the A_record class will implement the behaviours of these records, making it an active record. load_A is external to the class for obvious reasons - it is a type property, as opposed to a class one. It belong to the type. We could, in theory make it a static method, but it's not clear what benefits this will give. At least for now.

First attempts at joins

//case 2: C = A JOIN B -> C::{ A.a, A.b, A.c, B.a, B.b, B.c }
class C_Record {
var a_a, a_b, a_c;
var b_a, b_b, b_c;
...
}
//load similar to previous example, just add sql join


The problems with this approach are that it is hardcoded, inflexible, hard to maintain.
The good side is that it is simple and straightforward. Assuming that we can recognise (and we can using reflection) we can use a polymorphic call to load.

Let's go for take 2 (we can't use inheritance =( ):
class C_record {
private A; //we need to ensure that only variables of A_record can end up here
private B; //we need to ensure that only variables of B_record can end up here
....
//use your bag of tricks to keep objects of this class type safe:
// mostly _call(), _get(), _set() and various design patterns :)
}

Essentially we implement the association (join) operation using a special class. This is fine, but we now introduce an indirection via A and B and the extra calls to _call, _get or _set, which will slow down the whole thing.

Time for take 3.
$op['product'][] = 'A_record';
$op['product'][] = 'B_record';
....
$res = op_eval($op);
// Array('fields' => Array( ... ),
// 'load' => 'SELECT a.*, b.* FROM a,b',
// 'insert' => ..., 'update' => ... );

Basically we (somehow) build the type ops AST, produce load, update, insert, delete, .... SQL code. Cons - slow, but we can cache the result array for reuse, which will make the whole execution times faster, at least competitive with takes 1&2. Pros - flexible.

Take N. I might add more of these later.

The choice depends on the problem and flexibility at hand. There is no 'best' solution.