Skip navigation links

MySQL Forums :: Database Design & Data Modelling :: One subtable or multiple?


Advanced Search

One subtable or multiple?
Posted by: Colleen Boye ()
Date: April 13, 2012 12:24PM

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?

Options: ReplyQuote


Subject Written By Posted
One subtable or multiple? Colleen Boye 04/13/2012 12:24PM
Re: One subtable or multiple? Peter Brawley 04/13/2012 02:22PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.