Re: Polymorphic delete help needed

On Jul 6, 2007, at 2:31 AM, PFC wrote:

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

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.

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.

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.