What is the best way to store additional data in a Drupal 6 site pertaining to CCK node reference fields?
Suppose that a content type has a repeating node reference. I would like to be able to describe and/or annotate the node reference and describe the relationship between a "Person" content type and a "School" content type (e.g. "person attended school," "person donated to school"), or just have a chunk of text to go along with the relationship.
If you have a repeatable noderef field, you get a table like
CREATE TABLE `content_field_foo` ( `vid` int(10) unsigned NOT NULL default '0', `nid` int(10) unsigned NOT NULL default '0', `delta` int(10) unsigned NOT NULL default '0', `field_foo_nid` int(10) unsigned default NULL, PRIMARY KEY (`vid`,`delta`), KEY `nid` (`nid`), KEY `field_foo_nid` (`field_foo_nid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
I could create a module that maintains a table like the following, which is used to store the metadata.
CREATE TABLE `mymodule_noderef_metadata` ( `vid` int(10) unsigned NOT NULL default '0', `delta` int(10) unsigned NOT NULL default '0', `metadata` longtext, PRIMARY KEY (`vid`,`delta`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
The problem with this is that I would have to do a hook_nodeapi to update the table on both "nsert" and "update" to be able to handle revision rollbacks. In the particular implementation I am thinking of, there could be a pretty big performance impact of doing this.
Am I overthinking this? Is there a better way to do this?
I searched around for a module that does this, but didn't find anything.