MySQL dot-traversal... possible?

Question

So I'm working on a CMS that will allow users to "traverse" through database tables. I'm having some trouble implementing it though, and am wondering if anyone has any suggestions.

Table: house
name (varchar), town (relationship, using the rel table)

Table: town
name(varchar), state (relationship, using the rel table)

Table: state
name(varchar)

Table: rel
id1, id2, rel_type

The following example shows its intended usage. It reads, "Find 10 houses within the state of Alaska, ordered by name".

<?php
$Record = new Pod('house');
$Record->findRecords('name ASC', 10, "town.state.name = 'Alaska'");
?>

As a side note, I can easily find houses within a town name, since MySQL supports [table][dot][column_name]:

<?php
$Record = new Pod('house');
$Record->findRecords('name ASC', 10, "town.name = 'Great Falls'");
?>

I could always put a "state" field into the "house" table directly, but users need the ability to reference columns from other columns. Thanks in advance!

Answer 1

You need to use a join, for example:

SELECT
   house.*
FROM
   house
   JOIN town ON town.id = house.town
   JOIN state ON state.id = town.state
WHERE state.name = 'Alaska'

The query would work with 1:N relationships, but since you are using M:N relationships (implemented in a weird way), you will need to join a few more tables.

This makes the object model a little more complex, you will have to think about a new API for these kind of queries. Or use (see how it's done in) an existing ORM system.

To explain the 1:N vs M:N issue - it's not very common to have IDs pointing to other tables without foreign key constraints in well-designed databases. The usual way to model M:N relationships would be:

• house: id int (PK), name varchar
• house_town: house int (PK, FK references house.id), town int (PK, FK references town.id)
• town: id int (PK), name varchar

And for 1:N relationships:

• house: id int (PK), name varchar, town int (FK references town.id)
• town: id int (PK), name varchar