Creating Relationships between MySql tables

Question

Im trying to create relationships between four tables in MySql:

mainnodes (ID)
subnodes (ID)
tagrelationship (NODEID & TAGID)
tag (ID)

The table 'tagrelationship' references 'mainnodes' so when I try an insert, I can choose records from the 'mainnodes' table, However I want to be able to choose from the 'subnodes' table aswell.

I have tried setting up the table structure for 'tagrelationship' like so:

CREATE  TABLE IF NOT EXISTS `database`.`tagrelationship` (
`NODEID` INT(11) NOT NULL ,
 `TAGID` INT(11) NOT NULL ,
PRIMARY KEY (`TAGID`, `NODEID`) ,
INDEX `TAGS_TAGRELATIONSHIP` (`TAGID` ASC) ,
INDEX `SUB_TAGRELATIONSHIP` (`NODEID` ASC) ,
CONSTRAINT `TAGS_AGRELATIONSHIP`
  FOREIGN KEY (`TAGID` )
  REFERENCES `database`.`tags` (`ID` )
  ON DELETE CASCADE,
CONSTRAINT `MAINNODES_CMSTAGRELATIONSHIP`
  FOREIGN KEY (`NODEID` )
  REFERENCES `database`.`mainnodes` (`ID` )
  ON DELETE CASCADE,
CONSTRAINT `SUBNODES_CMSTAGRELATIONSHIP`
  FOREIGN KEY (`NODEID` )
  REFERENCES `database`.`subnodes` (`ID` )
  ON DELETE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

This executes fine but I am only able to select records from the 'subnodes' table not both.

How am I able to achieve this?

Thanks

Answer 1

The problem is your second NODEID CONSTRAINT is overwriting the first.

This is a polymorphic relationship you are looking to create, so one possible solution that still takes advantage of the database's foreign key constraints is to use a polymorphic "supertable" for both mainnodes and subnodes, called something like nodes:

CREATE  TABLE IF NOT EXISTS `database`.`nodes` (
`ID` INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ID`))

Then have each of your "subtables" reference this with a foreign key constraint:

CREATE  TABLE IF NOT EXISTS `database`.`mainnodes` (
...
`NODEID` INT(11) NOT NULL,
CONSTRAINT `MAINNODE_NODE_RELATIONSHIP`
  FOREIGN KEY (`NODEID` )
  REFERENCES `database`.`nodes` (`ID` )
  ON DELETE CASCADE,
...)

CREATE  TABLE IF NOT EXISTS `database`.`subnodes` (
...
`NODEID` INT(11) NOT NULL,
CONSTRAINT `SUBNODE_NODE_RELATIONSHIP`
  FOREIGN KEY (`NODEID` )
  REFERENCES `database`.`nodes` (`ID` )
  ON DELETE CASCADE,
...)

Finally, your tagrelationship table can just reference the supertable, nodes:

CREATE  TABLE IF NOT EXISTS `database`.`tagrelationship` (
...
CONSTRAINT `TAGS_AGRELATIONSHIP`
  FOREIGN KEY (`TAGID` )
  REFERENCES `database`.`tags` (`ID` )
  ON DELETE CASCADE,
CONSTRAINT `NODES_CMSTAGRELATIONSHIP`
  FOREIGN KEY (`NODEID` )
  REFERENCES `database`.`nodes` (`ID` )
  ON DELETE CASCADE,
...)

A simple but less robust solution is to simply remove the last two constraints about what NODEID can reference, and use your app code to enforce the constraint.