I was unable to get the composite key suggestion to work. Below is the MySql script for creating the table for the problem example:
Code:
`test`.CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE TABLE `test`.`table_a` (
`TableA_Id` decimal(10,0) NOT NULL,
`PropertyA` varchar(3) NOT NULL,
`AnotherProperty` varchar(45) NOT NULL,
PRIMARY KEY USING BTREE (`TableA_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `test`.`table_b` (
`PropertyA` varchar(3) NOT NULL,
`TableA_Id` decimal(10,0) NOT NULL,
`TableB_Id` decimal(10,0) NOT NULL,
`someString` varchar(45) NOT NULL,
PRIMARY KEY (`TableA_Id`,`TableB_Id`),
CONSTRAINT `FK_TableB_1` FOREIGN KEY (`TableA_Id`) REFERENCES `table_a` (`TableA_Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `test`.`table_c` (
`PropertyA` varchar(3) NOT NULL,
`TableA_Id` decimal(10,0) NOT NULL,
`TableC_Id` decimal(10,0) NOT NULL,
`someNumber` decimal(5,0) NOT NULL,
PRIMARY KEY (`TableA_Id`,`TableC_Id`),
CONSTRAINT `FK_TableC_1` FOREIGN KEY (`TableA_Id`) REFERENCES `table_a` (`TableA_Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
This is a scaled down version of my actual database to demonstrate the problem of mapping a value from one table to many tables. Any help with the mapping would be greatly appreciated.
Code:
Code:
Login
FAQ
Search
