Re: Polymorphic delete help needed

> I am doing a project using Ruby On Rails with PostgreSQL as the
> database.  I have not seen the term polymorphic used with databases
> except with Rails so I will quickly describe it.
>
> Instead of holding just an id as a foreign key, the record holds a
> "type" field which is a string and an id.  The string is the name of
> the table to which the id applies.  (That is slightly simplified).

    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

    So when I delete a Node, the derived class records are automatically
deleted in the auxiliary tables.
    Since there can be only one distinct node_id per node, you can put ON
DELETE CASCADE safely.

    Now, for your tree-thing, the fact that references are polymorphic isn't
important since they all refer to the same main "nodes" table.

    However as soon as you say "when a node no longer has any relations
pointing to it", then you get to choose between various flavors of garbage
collection and reference counting...

    Personally I like to move the deleted or orphaned rows to a "trash"
folder so they can be recovered if the user did delete the wrong node for
instance. Once in a while i "empty trash".

> 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 ?