www.webdeveloper.com
Results 1 to 2 of 2

Thread: Polymorphic DB Tables

Threaded View

  1. #1
    Join Date
    Dec 2006
    Posts
    48

    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:

    Code:
    ...
    
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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