One subtable or multiple?

[Colleen Boye]

Each of my main tables has an associated subtable for keeping track of locations; a typical location table looks like this:

CREATE TABLE `treatlocations` (
  `LocTreatSysNum` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `TreatNum` int(6) unsigned zerofill DEFAULT NULL,
  `SculptID` char(3) DEFAULT NULL,
  `Band` int(2) unsigned DEFAULT NULL,
  `BandType` char(3) DEFAULT NULL,
  `Column` int(2) unsigned DEFAULT NULL,
  `ColumnType` char(3) DEFAULT NULL,
  `Orientation` char(1) DEFAULT NULL,
  `MainElement` char(10) DEFAULT NULL,
  `MainElementNumber` int(2) unsigned DEFAULT NULL,
  `SubElement` char(10) DEFAULT NULL,
  `SubElementLetter` char(2) DEFAULT NULL,
  `ThirdElement` char(10) DEFAULT NULL,
  `ThirdElementNumber` int(2) unsigned DEFAULT NULL,
  `XVal` int(4) unsigned DEFAULT NULL,
  `YVal` int(4) unsigned DEFAULT NULL,
  `ZVal` int(4) unsigned DEFAULT NULL,
  PRIMARY KEY (`LocTreatSysNum`),
  KEY `LocTreatNum` (`TreatNum`),
  KEY `TreatLocSculptID` (`SculptID`),
  CONSTRAINT `LocTreatNum` FOREIGN KEY (`TreatNum`) REFERENCES `treatments` (`TreatNum`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `TreatLocSculptID` FOREIGN KEY (`SculptID`) REFERENCES `sculptures` (`SculptID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

It seems clumsy to have a bunch of tables with the same columns, so I was thinking of combining them. I'd just have to add a field for the referenced table name and change the TreatNum field to something more generic, like RecordNum.

The trouble is that RecordNum could no longer be a foreign key because it would be referencing a bunch of tables instead of just one. Is it worth losing the foreign keys for the sake of combining my redundant tables, or should I leave it the way it is?