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.
