Foreign key reference in the same column

Question

I am developing a bd schema for mysql. My db stores 3 kinds of points: a, b or c, and the path consists of n pairs of points:

Route = [(a1 or b1 or c1; a2 or b2 or c2), (a2 or b2 or c2; a3 or b3 or c3), ...]

create table a_points ( point_id serial not null, owner_id bigint unsigned not null, name varchar(20) not null, primary key (point_id), foreign key (owner_id) references othertable (other_id) ) engine = InnoDB; create table b_points ( point_id serial not null, owner_id bigint unsigned not null, name varchar(20) not null, fields varchar(20) not null, primary key (point_id), foreign key (owner_id) references othertable (owner_id) ) engine = InnoDB; create table c_points ( point_id serial not null, name varchar(20) not null, cfields varchar(20) not null, primary key (point_id) ) engine = InnoDB; create table paths ( path_id serial not null, name varchar(20) not null, primary key (path_id) ) engine = InnoDB; create table point_pairs ( pair_id serial not null, path_id bigint unsigned not null, point_from bigint unsigned not null, point_to bigint unsigned not null, table_from varchar(9) not null, table_to varchar(9) not null, primary key (pair_id), foreign key (path_id) references paths (path_id) ) engine = InnoDB; 

(*) a pair of points is (m, n) or from m to n

So, I save a couple of points along with their path identifier. My problem is that I had to create two columns identifying table names m and n. table_from for m and table_to for n. Thus, I will have to use these two columns in my code to find out which points are stored in the route (path and point_pairs table). My question is: does MySql provide something to reference foreign keys in a single column? I already thought about attaching points a, b, and c to the tables, but I would have to add a type column to this new table and my php classes would become useless.

Thanks in advance!

Answer 1

You are using the Polymorphic Associations template, and no, there is no way to do this and use foreign keys to provide referential integrity.

I suggest you make one common table containing the link a_points , b_points and c_points . Then your point pairs can refer to this general table.

 a_points --> b_points --> common_points <-- point_pairs c_points --> 

In other words, a way to create polymorphic associations is to change the direction of a link.