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

Ben Lee · Accepted Answer · 2012-03-21T01:09:02.613

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.

Thanks for the reply. When I add the index to the nodes table I get the following warning: The indexes PRIMARY and NODE_ID seem to be equal and one of them could possibly be removed. This still ok? — Rob Fyffe, Mar 21 '12 at 01:03
@Robert, oops my mistake. Yeah, should only have the PRIMARY index there. Updated my answer. — Ben Lee, Mar 21 '12 at 01:09
No problem. I'm not sure my approach will work now any ways. Basically I am trying to setup a custom content section within Umbraco. Not sure if you have experience with it but Basically when it creates a default node/doc it appears to adds records to 3 tables in the following order: umbraconode cmscontent cmsdocument Then the 'tagsrelationship' table references the 'umbraconode' table. In umbraco tutorials they created one table for the custom content nodes and they don't reference the tables above at all. Not sure weather the table should be linked to the 3 tables above or not. — Rob Fyffe, Mar 21 '12 at 02:24

Creating Relationships between MySql tables

1 Answers1

Linked