What are pivot tables in layman terms?

I think my problem calls for a pivot table but I want to be clear before spending a huge amount of time trying to implement. I have not yet used pivot tables for any projects.

I have a new project that contains multiple "kits" that have common "parts" among them and in some cases a part can be interchanged with those for another kit. Likewise not all parts are compatible with one another.

Rather than having to create some nightmarish SQL query I was hoping to essentially build a table that would store compatibility information to make cross referencing a simple SQL query.

Example:

SELECT kitID FROM crossRefTable WHERE partA, partC and partE are true.

Or for example:

SELECT partID FROM crossRefTable WHERE partA and partE are true.

(Yes, the SQL queries are exmple only for stressing the point)

I am not sure that the "belongTo" and "hasMany" necessarily apply here or if so how would that be implemented?

Coding is in PhalconPHP using MongoDB.

Let's say you have 23 kits and 512 distinct parts. How do you define that kit 1 uses parts A, B, and C while kit 2 uses parts A, D, E, and F? A pivot table is a good solution for defining the relationship between each kit and its parts. Once you have a Kits table, a Parts table and a KitParts table, then you can join the three together for easy queries. Once joins become second nature to you, they're actually a pleasure to work with.

In terms of PhalconPHP, hasMany and belongsTo (note it has an s) combined with a pivot table is a great solution. In the pivot table you'd reference a kit's id and a part's id.

Let's say the user is currently viewing a list of all kits and they click a specific kit, now you'd like to display all the parts is contains. Your example is almost exactly the same situation as the Phalcon manual shows. But in terms of how I'd personally write it, I'd take more the following route:

(Note: this is all untested and I haven't touched phalcon in a few months)

//Use your BaseModel to take some of the weight off your Models.
//Nobody likes dirty Models, just ask Victoria's Secret. :p
//BaseModels are a good place to add your own methods,
//that way all models have access to them.
class BaseModel extends \Phalcon\Mvc\Model
{
}

class Kits extends Basemodel
{
    public $id;
    public $name;
    public function initialize()
    {
        $this->hasMany('id', 'KitParts', 'kit_id', array(
        'foreignKey' => array(
        'message' => 'Kit cannot be deleted because it\'s used by KitParts'
        )
        ));
    }
}

//Pivot table
class KitParts extends BaseModel
{
    public $id;
    public $kit_id;   //primary key used in Kits.id
    public $part_id;  //primary key used in Parts.id
    $this->belongsTo('kit_id', 'Kits', 'id');
    $this->belongsTo('part_id', 'Parts', 'id');
}

class Parts extends BaseModel
{
    public $id;
    public $name;
    public function initialize()
    {
        $this->hasMany('id', 'KitParts', 'part_id', array(
        'foreignKey' => array(
        'message' => 'Part cannot be deleted because it\'s used by KitParts'
        )
        ));
    }
}

Then to display all parts for a particular kit, you'd write:

$kit = Kits::findFirst(123);//Find record with id = 123
foreach ($kit->kitParts as $kitPart)
{
    echo htmlentities($kitPart->parts->name), "<br>\n";
}

You could also add a method to your Kits class to make life easier: (Again, this is untested... refer to the documentation if something is wrong.)

//Add this as a method to the "Kits" class
public function getParts()
{
    $results = array();
    foreach ($this->kitParts as $kitPart)
    {
        $results[] = $kitPart->parts;
    }
    return $results;
}

Then you'd do:

$kit = Kits::findFirst(123);//Find record with id = 123
$parts = $kit->getParts();
if(count($parts) > 0)
{
    echo "<strong>Parts</strong><br>\n";
    foreach($parts as $part)
    {
        echo htmlentities($part->name),"<br>\n";
    }
}else{
    echo "No parts to show.<br>\n";
}