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 ?
