MySQL Schema Design: One Possible Values Between Multiple Columns I'm working on a schema for a MySQL database. I have tables which are derivatives of one another. For example, rather than a single "animals" table, I might have separate "mammals" and "birds" tables:

CREATE TABLE `mammals` (
  `id` INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `species_name` VARCHAR(255) NOT NULL,
  `fur_color` VARCHAR(255) NOT NULL
);

CREATE TABLE `birds` (
  `id` INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `species_name` VARCHAR(255) NOT NULL,
  `wingspan` DECIMAL(11,8) NOT NULL DEFAULT 0
);


The tables are separate because a large percentage of the combined columns between the two tables are unique to one specific derivative (e.g. fur_color and wingspan in the example above). The different derivatives also have very different relationships to other tables.

However in some other tables we might want to reference a specific instance of one of those entities:

CREATE TABLE `pets` (
  `id` INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `owner_name` VARCHAR(255) NOT NULL,
  `mammal_id` INT(11) UNSIGNED ZEROFILL NOT NULL,
  `bird_id` INT(11) UNSIGNED ZEROFILL NOT NULL
);


The above example would allow us to specify what species of animal each pet is, but there's a problem: the pets schema allows for values to be entered in both the mammal_id and bird_id column while in reality I only want to allow input in one or the other.

What is the best solution to this issue? I was considering a check constraint but according to the MySQL Reference Manual:


  The CHECK clause is parsed but ignored by all storage engines.


Should I simply replicate the constraint as a trigger that throws an error, or is there a better solution that I'm not thinking of?
id, species_name, dominant_color, owner_id,  
type ENUM('mammal', 'bird', 'reptile', 'arachnid', ...),  
other_characteristics TEXT


Where other_characteristics has JSON in it.  You can add whatever you want there.

Other tips:  Do not blindly use (255); use some reasonable limit.  Once wingspan is in the JSON, you can get rid of the ridiculously precise DECIMAL(11,8).