MYSQL - One Column Referenced to Multiple Table

Question

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

Timeworn

Do mean that the column can be referred to by multiple other tables as a foreign key, or that the column can refer to multiple other tables, as in multiple foreign key constraints on a single column? – Inquietude

There is nothing to stop you from creating multiple foreign key constraints. Though I can't think of why this should be necessary – Sweyn

@Michael Yes I mean "column can refer to multiple other tables, as in multiple foreign key". – Timeworn

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?

Where

Hi, @Roemer! Could you tell me more detail about why it is NOT good practice? – Codfish

Because it makes coding unnecessary complicated and it is not standard so who maintains it must exactly know what is going on. And you never know who will maintain something a few years from now. And even if it is yourself you will probably have forgotten. You want to stay with standard practice to be able to maintain, expand and code without complications. – Where

Hi, @Roemer, could you tell me what would be the best (correct) approach for something similar instead of this way? – Babbie

Hi Oscar, just use a separate foreign key for every join. OR, If you have millions of records and dont want many columns (like this foreign id's) in it, if many of those columns will often be empty, use a separate table "in between". You will link it 1-on-1 to the main table. You could even use the same primary keys (same pkey name, same id's) to link it. Have these foreign id's and maybe more rarely used columns in this separate table that will have WAY less records. That way you can have a main table with 100 mln records and the extra table with just 5000. Just left join them when needed. – Where

Answer 2

If you mean "can a column in one table be used as a foreign key in multiple tables", then the answer is YES. This is the whole point of a relational database

Chartography

I mean "a column can refer to multiple other tables" – Timeworn

Answer 3

Yes, you can do that so. here is a sample on how to do it:

Here is the table that has a column(CountryID) that will be referenced by multiple tables:

CREATE TABLE DLAccountingSystem.tblCountry
(
    CountryID       INT             AUTO_INCREMENT NOT NULL PRIMARY KEY,
    CountryName     VARCHAR(128)    NOT NULL,
    LastEditUser    VARCHAR(128)    NOT NULL,
    LastEditDate    DATETIME        NOT NULL
) ENGINE=INNODB;

Here are the tables that is going to reference the column(CountryID):

CREATE TABLE DLAccountingSystem.tblCity
(
    CityID          INT             AUTO_INCREMENT NOT NULL PRIMARY KEY,
    CountryID       INT             NOT NULL,
    CityName        VARCHAR(128)    NOT NULL,
    LastEditUser    VARCHAR(128)    NOT NULL,
    LastEditDate    DATETIME        NOT NULL
) ENGINE=INNODB;

CREATE TABLE DLAccountingSystem.tblProvince
(
    ProvinceID      INT             AUTO_INCREMENT NOT NULL PRIMARY KEY,
    CountryID       INT             NOT NULL,
    ProvinceName    VARCHAR(128)    NOT NULL,
    LastEditUser    VARCHAR(128)    NOT NULL,
    LastEditDate    DATETIME        NOT NULL
) ENGINE=INNODB;

Here is how you create a reference to the column:

 ALTER TABLE DLAccountingSystem.tblCity
 ADD CONSTRAINT fk_tblcitycountryid FOREIGN KEY CountryID (CountryID)
 REFERENCES DLAccountingSystem.tblCountry (CountryID)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION

 ALTER TABLE DLAccountingSystem.tblProvince
 ADD CONSTRAINT fk_tblprovincecountryid FOREIGN KEY CountryID (CountryID)
 REFERENCES DLAccountingSystem.tblCountry (CountryID)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION

here is a table that has column that references different columns from (CountryID, ProvinceID, CityID) multiple tables(I Don't personally advice this way of table structuring. Just my opinion no offense ;) )

CREATE TABLE DLAccountingSystem.tblPersons
(
    PersonID       INT          AUTO_INCREMENT NOT NULL PRIMARY KEY,
    PlaceID        INT          NOT NULL,
    PlaceTypeID    INT          NOT NULL, -- this property refers to what table are you referencing.
 //Other properties here.....
) ENGINE=INNODB;

you should also have a lookup table that would contain the PlaceType:

CREATE TABLE DLAccountingSystem.tblPlaceType
(
    PlaceTypeID       INT          AUTO_INCREMENT NOT NULL PRIMARY KEY,
    PlaceTypeName        INT          NOT NULL
 //Other properties here.....
) ENGINE=INNODB;

here is how you fetch it:

SELECT p1.PersonID,
       tcity.CityName,
       tprov.ProvinceName,
       tcoun.CountryName
FROM DLAccountingSystem.tblPersons p1 
LEFT JOIN (SELECT p2.PersonID, p2.PlaceTypeID, c.CityName FROM DLAccountingSystem.tblPersons p2 INNER JOIN DLAccountingSystem.tblCity c ON p2.ObjectID = c.CityID WHERE PlaceTypeID = @CityTypeID) tcity ON p1.PersonID = tcity.PersonID
LEFT JOIN (SELECT p2.PersonID, p2.PlaceTypeID, c.ProvinceName FROM DLAccountingSystem.tblPersons p2 INNER JOIN DLAccountingSystem.tblProvince c ON p2.ObjectID = c.ProvinceID WHERE PlaceTypeID = @ProvinceTypeID) tprov ON p1.PersonID = tprov.PersonID
LEFT JOIN (SELECT p2.PersonID, p2.PlaceTypeID, c.CountryName FROM DLAccountingSystem.tblPersons p2 INNER JOIN DLAccountingSystem.tblCountry c ON p2.ObjectID = c.CountryID WHERE PlaceTypeID = @CountryTypeID) tcoun ON p1.PersonID = tcoun.PersonID

you can select from other tables like

Battue

I mean "a column can refer to multiple other tables" – Timeworn

yes this is also possible. but it would be very hard to maintain relational integrity in implementing that.. we have something like that in our database used for notifications. can you please give me the exact entity set? – Battue

Answer 4

A same column or set of columns can act as a parent and/or as a child endpoint of a foreign key or foreign keys.

Abixah