Is there an alternative to MySQL Table with 26 foreign keys

Question

I have an InnoDB MySQL database with a table that should be able to connect to one of 26 other tables through a foreign key. Each entry will only connect to one of these 26 at a time. The table is likely to consist of no more than 10,000 entries. Is there an alternative way to do this?

-- ----------------------------------------------------- -- Table `db_mydb`.`tb_job` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `db_mydb`.`tb_job` ( `job_id` INT(11) NOT NULL AUTO_INCREMENT , // Removed 26 other fields that the table requires `job_foreignkey_a_id` INT(11) NULL DEFAULT NULL , `job_foreignkey_b_id` INT(11) NULL DEFAULT NULL , `job_foreignkey_c_id` INT(11) NULL DEFAULT NULL , // Removed the other 23 foreign keys fields that are the same PRIMARY KEY (`job_id`) , CONSTRAINT `fka_tb_job_tb` FOREIGN KEY (`job_foreignkey_a_id` ) REFERENCES `db_mydb`.`tb_foreignkey_a` (`foreignkey_a_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fkb_tb_job_tb` FOREIGN KEY (`job_foreignkey_b_id` ) REFERENCES `db_mydb`.`tb_foreignkey_b` (`foreignkey_b_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fkc_tb_job_tb` FOREIGN KEY (`job_foreignkey_c_id` ) REFERENCES `db_mydb`.`tb_foreignkey_c` (`foreignkey_c_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) // Removed the other 23 foreign keys constraints that are the same ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE INDEX `fka_tb_job_tb` ON `db_mydb`.`tb_job` (`job_foreignkey_a_id` ASC) ; CREATE INDEX `fkb_tb_job_tb` ON `db_mydb`.`tb_job` (`job_foreignkey_b_id` ASC) ; CREATE INDEX `fkc_tb_job_tb` ON `db_mydb`.`tb_job` (`job_foreignkey_c_id` ASC) ; // Removed the other 23 foreign keys indexes that are the same 

Answer 1

This is a common foreign key problem that MySQL and friends usually do not support. There are two ways to do this.

First, how you did it are nullable foreign keys, one for each type.

The other, as in Django Content Types , must have a connection table, each row has a row identifier and a field that indicates the table to search. Then your code should formulate an SQL query depending on the contents of the field. It works well, but has limitations:

The downside of the first is bloat, but it brings you problems with normal FKs, i.e. referential integrity and SQL joins, etc., which are very valuable. You cannot get them with the second method.

Answer 2

It depends, if you want to keep the foreign key constraint, you can have one table that refers to one of the tables by the type of key or table. The problem is that you will lose the foreign key constraint. Of course, if you can create a function-based constraint, then it may work for you. Or you can force a connection using a trigger. Function-based restrictions are not available in mysql.

Answer 3

Yes you can do it. These two StackOverflow answers illustrate the fundamentals in a slightly different context.

• The same data from different objects in the database - best practice - example phone numbers
• Different types of users / native content objects in one table - how?

Using MySQL, you need to replace the critical constraints of CHECK () with foreign key references. This does not work in the most general case for MySQL, but it works in this particular application.

If this is not enough to get the information, leave me a comment and I will try to expand this answer a little more.