Database Normalization

[Vint Maggs]

Kind People of the Internet,
I am attempting to design a simple database containing 4 Innodb tables using MySQL 5.0.21. As database design is not my forte, I would like your opinion. I think the fields in each table are correct (eg no duplicate data) but I am particularly concerned with the FK-PK table relationships. Do the Key fields need to be in a particular order? Are compound keys the way to go? As you can see, I am relating the *_ID fields from the child table to the corresponding *_ID fields in the parent table (compound keys) but I am not sure this is the right thing to do.

In general, TempMod table is the parent to TempModEPN table which is the parent to TempModBlock table, which in turn is the parent to TempModParameter table. For each unique TempMod, there can be up to 4 EPNs. Each EPN can have 1 to 16 Blocks, and each Block can contain up to 18 Parameters.

DROP TABLE IF EXISTS `test`.`tempmod`;
CREATE TABLE `test`.`tempmod` (
`T_ID` int(10) unsigned NOT NULL auto_increment,
`TempModNumber` varchar(13) NOT NULL,
`DateCompleted` date NOT NULL,
`CompletedBy` varchar(3) NOT NULL,
`DateExpires` date NOT NULL,
`DateRemoved` date default NULL,
`RemovedBy` varchar(3) default NULL,
`SMPK` varchar(14) default NULL,
`SMPKDate` date default NULL,
PRIMARY KEY (`T_ID`),
UNIQUE KEY `TempModNumber_Unique` (`TempModNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `test`.`tempmodepn`;
CREATE TABLE `test`.`tempmodepn` (
`E_ID` int(10) unsigned NOT NULL auto_increment,
`T_ID` int(10) unsigned NOT NULL default '0',
`EPN` varchar(45) NOT NULL,
PRIMARY KEY USING BTREE (`E_ID`,`T_ID`),
KEY `TID_INDEX` USING BTREE (`T_ID`),
CONSTRAINT `FK_tempmod` FOREIGN KEY (`T_ID`) REFERENCES `tempmod` (`T_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `test`.`tempmodblock`;
CREATE TABLE `test`.`tempmodblock` (
`B_ID` int(10) unsigned NOT NULL auto_increment,
`T_ID` int(10) unsigned NOT NULL default '0',
`E_ID` int(10) unsigned NOT NULL default '0',
`BLOCK` varchar(45) NOT NULL,
PRIMARY KEY USING BTREE (`B_ID`,`T_ID`,`E_ID`),
KEY `TID_INDEX` USING BTREE (`T_ID`),
KEY `EID_INDEX` USING BTREE (`E_ID`),
KEY `FK_EPN` (`E_ID`,`T_ID`),
CONSTRAINT `FK_EPN` FOREIGN KEY (`E_ID`, `T_ID`) REFERENCES `tempmodepn` (`E_ID`, `T_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;


DROP TABLE IF EXISTS `test`.`tempmodparameter`;
CREATE TABLE `test`.`tempmodparameter` (
`P_ID` int(10) unsigned NOT NULL auto_increment,
`B_ID` int(10) unsigned NOT NULL default '0',
`E_ID` int(10) unsigned NOT NULL default '0',
`T_ID` int(10) unsigned NOT NULL default '0',
`Parameter` varchar(45) NOT NULL,
`CurrentValue` varchar(45) NOT NULL,
`ModifiedValue` varchar(45) NOT NULL,
PRIMARY KEY USING BTREE (`P_ID`,`B_ID`,`E_ID`,`T_ID`),
KEY `FK_BLOCK` (`B_ID`,`T_ID`,`E_ID`),
CONSTRAINT `FK_BLOCK` FOREIGN KEY (`B_ID`, `T_ID`, `E_ID`) REFERENCES `tempmodblock` (`B_ID`, `T_ID`, `E_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Thanks for your support.
Vint