Results 1 to 2 of 2

Thread: Polymorphic DB Tables

  1. #1
    Join Date
    Dec 2006

    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).


    Either way, the usage will be the same. So far, something like this:

    class Notification extends DBData {
    	const childTables = "NewGameNotification, ChatNotification";
    	const columnsDDL = "time int, playerID int";
    	public $time, $playerID;
    class ChatNotification extends Notification {
    	const childTables = "ColoredChatNotification, EmotedChatNotification";
    	const columnsDDL = 'fromPlayerID int, text text';
    	public $fromPlayerID, $text;
    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.
    Last edited by Verdagon; 08-29-2009 at 09:28 AM.

  2. #2
    Join Date
    Aug 2004
    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.
    ~ Terry Pratchett in Nation


Thread Information

Users Browsing this Thread


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center

Recent Articles