sql - MySQL dot-traversal... possible? -

So I'm working on a CMS that will allow users to "transfer" via the database tables. Although I have some difficulty in implementing it, and am wondering if there is any suggestion Table: Home name (varchar), city (using relation, rel table) Table: City name (varchar) Table of contents (relation using Reliance Energy Table) Table: State Name (varchar) Table: rel id1, id2, rel_type

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

  & lt ;? Php $ record = new Pod ('house'); $ Record-> FindRecord ('ASC Name', 10, 'town.state.name =' Alaska ''); ? & Gt;  

As a side note, I can easily find homes inside the city's name because MySQL supports [table] [dot] [column_name]:

  & lt; Php $ record = new pod ('home'); $ Record- & gt; Search Line ("Name ASC", 10, "Town. Name = 'Great Falls'');? & Gt;  

I always put the" State "field directly in the" Home "table Users can, however, require the ability to refer to columns from other columns. Thanks in advance!

You need to use, for example:

  Select home. JOIN city from town.id = house.town to join state on state. Id = town.state Where are you Jn.name = 'Alaska'  

1: Work with N relationships, but since you are using M: N relationships (implemented in an odd way) , You have to join some more tables.

This makes the object model a bit more complicated, you have to think of a new API for these types of questions. Or use an existing ORM system (See how this is done).

1: N vs. M: For issuing N - this is not common with ID, which would be the common method for the database M: N Relationship designed to point to other tables without foreign key constraints:

• City: id int (PK), name varchar
• Home: ID int (PK), name varchar
• house_town: house int (pk, Li>

and 1: N for relationships: / P>

• Home: id int (pk), name varchar, city Int (FK reference city.id)
• City: id int (pk), name varchar