0

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?

0
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).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.