If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Polymorphic DB Tables

(Summary: I want to have an is-a relationship in PHP also be in the database, and if I search for something in the table of the base class, it will also search in the table of the child class. I also need this treatment with updating, inserting, and deleting. Does anyone know of a framework that will do this?)

So, I've been dealing with some pretty crazy ideas. But I'm absolutely positive that others have thought of this and perhaps even implemented it, though google and forum searches reveal nothing.

In PHP, I have a base class:
- Person, with property "name".
Two subclasses of Person:
- Programmer, with property "IQ" and inherited property "name".
- Quarterback, with property "Apishness" and inherited property "name".

The question is how to represent this class "Tree" in the database.

=== Implementation 1 ===

In the database, there is one table:
- Person, column "type", column "name", optional column "IQ", and optional column "Apishness"

The idea is that the database be able to:
- Pay attention to the "IQ" column only if "type" = "Programmer" (likewise for Apishness/Quarterback).
- Only look through the Programmer subset when I only want a Programmer (likewise for Quarterback).
- Instantiate a Programmer object if the row is a Programmer (likewise for Quarterback).

=== Implementation 2 ===

In the database, there are three tables:
- Person, column "name".
- Programmer, columns "name" and "IQ"
- Quarterback, columns "name" and "Apishness"

The idea is that the database be able to:
- Look through all three tables when I want a Person.
- Look through only the Programmer table when I want Programmers (likewise for Quarterback).
- Instantiate a Programmer object when I recall from the Programmer table (likewise for Quarterback).

I hope by now, the idea is clear: I want to be able to have a Person, Quarterback, or Programmer object, be able to put it in the database, bring it back just as it was. I also want the luxury to be able to treat them all as Person, because they are all of type Person, after all.

So, my official question is, is there a framework that will do this for me? I've already finished implementation 1, and am almost done with implementation 2, over 1000 lines already.

The reason I need to know this is because either implementation has its drawbacks, implementation 1 has a lot of wasted space, but uses less queries, and is faster for small amounts of data. Implementation 2 is better for large amounts of data, but uses more queries, and can't do things like LIMIT, or ORDER without falling back on implementation 1 temporarily.

I would define a "class" table that only has information about the class (not its properties). This table would be implemented to represent the hierarchical relationship between parent/child classes using either the "adjacency list" or "nested set" model. See http://dev.mysql.com/tech-resources/...ical-data.html for more info. (I'd go with the nested set approach, myself, as being more flexible/powerful.)

Then I'd have a separate table for properties, which would include a foreign key relationship to its class's primary key. Assuming your DBMS version/storage-engine supports it, you could use foreign key triggers to handle things like deleting all of a class's properties when a class is deleted.