MYSQL - One Column Referenced to Multiple Table

Question

Can a single column in a table can be referenced to multiple tables?

Answer 1

A very late answer, but for whoever is wondering & googeling.

YES this can be done, but it is NOT good practice and even though it is quite simple, it will probably blow up in your face if you're not very aware of what you are doing. Not recommended.

However, I can see uses. For instance, you have a large table of millions of records, and you want in exceptional cases link to unknown or multiple tables (in which case it better be many). With multiple tables, if you would make a foreign key for all of them, that would be a huge bloat in your database size. An unknown table would be possible for instance in a technical support system, where you want to link to record in a table where there might be a problem, and this could be (almost) all tables in the database, including future ones.

Of course you will need two fields to link with: a foreign key field and the name of the table it is linking to. Lets call them foreignId and linkedTable

linkedTable could be an enum or a string, preferrably enum (less space), but that's only possible if the different tables you want to link to, are fixed.

Let's give an extremely silly example. You have an enormous user table users of which some user can add exactly one personal set of data to their profile. This can be about a hobby, a pet, a sport they practice or their profession. Now this info is different in all four cases. (4 possible tables is in reality not enough to justify this structure)

Now let's say linkedTable is an enum with possible values pets, hobbies, sports and professions, which are the names of four differently structured tables. Let's say id is the pkey in all four of them.

You join for instance as follows:

SELECT * FROM users 
    LEFT JOIN  pets        ON linkedTable = 'pets'        AND foreignId = pets.id
    LEFT JOIN  hobbies     ON linkedTable = 'hobbies'     AND foreignId = hobbies.id
    LEFT JOIN  sports      ON linkedTable = 'sports'      AND foreignId = sports.id
    LEFT JOIN  professions ON linkedTable = 'professions' AND foreignId = professions.id

This is just to give a basic jest. Since you probably only need the link in rare cases, you will more likely do the lookup in your programming language, like PHP, when you loop through the users (without join).

Want to try out? You can try it yourself with building this test database (make sure you use a test database):

CREATE TABLE IF NOT EXISTS `users` (
    `id` INT NOT NULL AUTO_INCREMENT , 
    `name` VARCHAR(100) NOT NULL , 
    `linkedTable` ENUM('pets','hobbies','sports','professions') NULL DEFAULT NULL , 
    `foreignId` INT NULL DEFAULT NULL , 
  PRIMARY KEY (`id`), INDEX (`linkedTable`)
) ;

CREATE TABLE  IF NOT EXISTS `pets` ( 
    `id` INT NOT NULL AUTO_INCREMENT , 
    `animalTypeId` INT NOT NULL , 
    `name` VARCHAR(100) NOT NULL , 
    `colorId` INT NOT NULL , 
  PRIMARY KEY (`id`), INDEX (`animalTypeId`), INDEX (`colorId`)
) ;

CREATE TABLE  IF NOT EXISTS `hobbies` ( 
    `id` INT NOT NULL AUTO_INCREMENT , 
    `hobbyTypeId` INT NOT NULL , 
    `hoursPerWeekSpend` INT NOT NULL , 
    `websiteUrl` VARCHAR(300) NULL , 
  PRIMARY KEY (`id`), INDEX (`hobbyTypeId`)
) ;

CREATE TABLE  IF NOT EXISTS `sports` ( 
    `id` INT NOT NULL AUTO_INCREMENT , 
    `sportTypeId` INT NOT NULL , 
    `hoursPerWeekSpend` INT NOT NULL , 
    `nameClub` VARCHAR(100) NULL , 
    `professional` TINYINT NOT NULL DEFAULT 0, 
  PRIMARY KEY (`id`), INDEX (`sportTypeId`)
) ;

CREATE TABLE  IF NOT EXISTS `professions` ( 
    `id` INT NOT NULL AUTO_INCREMENT , 
    `professionId` INT NOT NULL , 
    `hoursPerWeek` INT NOT NULL , 
    `nameCompany` VARCHAR(100) NULL , 
    `jobDescription` VARCHAR(400) NULL, 
  PRIMARY KEY (`id`), INDEX (`professionId`)
) ;


INSERT INTO `users` (`id`, `name`, `linkedTable`, `foreignId`) 
   VALUES 
   (NULL, 'Hank', 'pets', '1'), 
   (NULL, 'Peter', 'hobbies', '2'), 
   (NULL, 'Muhammed', 'professions', '1'), 
   (NULL, 'Clarice', NULL, NULL), 
   (NULL, 'Miryam', 'professions', '2'), 
   (NULL, 'Ming-Lee', 'hobbies', '1'), 
   (NULL, 'Drakan', NULL, NULL), 
   (NULL, 'Gertrude', 'sports', '2'), 
   (NULL, 'Mbase', NULL, NULL);


INSERT INTO `pets` (`id`, `animalTypeId`, `name`, `colorId`) 
VALUES (NULL, '1', 'Mimi', '3'), (NULL, '2', 'Tiger', '8');

INSERT INTO `hobbies` (`id`, `hobbyTypeId`, `hoursPerWeekSpend`, `websiteUrl`) 
VALUES (NULL, '123', '21', NULL), (NULL, '2', '1', 'http://www.freesoup.org');

INSERT INTO `sports` (`id`, `sportTypeId`, `hoursPerWeekSpend`, `nameClub`, `professional`) 
VALUES (NULL, '2', '3', 'Racket to Racket', '0'), (NULL, '12', '34', NULL, '1');

INSERT INTO `professions` (`id`, `professionId`, `hoursPerWeek`, `nameCompany`, `jobDescription`) 
VALUES (NULL, '275', '40', 'Ben & Jerry\'s', 'Ice cream designer'), (NULL, '21', '24', 'City of Dublin', 'Garbage collector');

Then run the first query.

Fun note for discussion: How would you index this?