Re: Polymorphic delete help needed - Mailing list pgsql-general

O>>     Here is how I implemented something very similar (in PHP) :
>>
>>     - "Node" class and several derived classes.
>>     - "nodes" table which contains the fields for the base class with
>> node_id as a PK and a field which indicates the class
>>     - "nodes_***" tables which contain the extra fields for the derived
>> class "***", having node_id as a primary key.
>>
>>     As you see this is very similar to what you got.
>>     All the "nodes_***" tables have :
>>     node_id PRIMARY KEY REPERENCES nodes( id ) ON DELETE CASCADE


> Your method is not exactly what I am doing because I do not have the
> "nodes" table.  I have only the "nodes_***" tables.  But, I believe your
> approach has many advantages.

    OK, I see.
    The advantage of my approach is that :

    - all common fields that are shared by all derived classes are stored in
the "base" nodes table
    - the PK on this table uniquely identifies any instance, whatever its
object class
    - the tables holding the derived classes's extra members (ie columns)
merely refer to it via a FK which is clean

    When you want to get a Node instance by its id, the ORM grabs the row in
the main table, which contains the type information, then instantiates the
right object class, grabs the extra row in the extra tables (which shares
the same id), and gives this data to the instantiated object which then
uses it to populate its member variables.

    Therefore, there can be no duplicates, and you only need to look into one
table to know if an object with a specific id exists or not, or to delete
it.

    Say Nodes can be, for instance, Photos, or Albums, or Articles ; say an
Album contains Photos and Articles and you want to display its contents :

    - select from relations r JOIN nodes n ON n.id=r.child_id WHERE
r.parent_id = (id of album)
    => gets the ids of children along with extra info stored in the relations
table (like sort order, etc)

    - scan the result set and group it by object class
    - for each object class :
        - grab the rows into the extra tables for the ids of objects of this
class using SELECT WHERE id IN (...)
        - instantiate and populate

    So if you have an Album with 5 Articles and 10 Photos, you do 3 queries -
    - get 15 rows from Nodes JOIN relations
    - get 5 rows from nodes_articles
    - get 10 rows from nodes_photos

    It's pretty efficient. I believe you can tweak Rails' ORM into doing that.

    FYI it's a PHP ORM that I wrote over the course of various projects.
Unfortunately PHP is very bad at generic/meta programming, so the
implementation is quite convoluted, and I did not opensource it because
the documentation would be quite an effort to write ;)

> Rails has an inheritance ability but they do it differently.  They
> simply have a "nodes" table with all the "nodes_***" tables smashed in
> to it.  I did not like that approach at all.

    Hm, another reason why I don't really like the Rails approach... do they
at least use unique IDs ? Please say yes ;)

> But doing what you are doing, I believe I can very nicely fit into Rails
> and (obviously) PostgreSQL.  Plus, your suggestion of moving entries to
> a "trash" bin seems very wise.

>>> The first problem that creates is it makes it hard to do a constraint
>>> on the name/id pair.  I thought about writing a function that would
>>
>>     Is this ClassName / id only found in the "main" table or is every FK
>> implemented as a ClassName / id pair ?
>
> The ClassName / id pair is found only in the relationships table.  There
> are two instances of it however: parent and child.

    I believe duplicating the ClassName and storing it everywhere to be bad.
With my approach you only need to use the id in reference since two
instances of the same base class cannot have the same id regardless of
their class, and you only need the id to instantiate a row, whatever its
class.

    If you want to specify that a class instance can only have children of
specific classes (or their subclasses), for example a FriendList can only
contain instances of People, or whatever, you can use a constraint trigger
which will check the class of the referenced row against a table
describing the allowed parent/child combinations.