Tutorial :Referencing foreign keys in the same column



Question:

I'm designing a bd-scheme for mysql. My db stores 3 kinds of points: a, b or c and a path is composed by n pair 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 point is (m, n) or from m to n

So I'm storing pair of points together with their path's id. My problem is that I had to create two columns identifying the tables' name of m and n. table_from for m and table_to for n. Thus I'd have to use these two columns in my code to know what kind of points are saved in a route (path and point_pairs table). My question: Does MySql provide something for referencing n foreign keys in the same column? I already thought about joining a, b and c point tables but I'd have to add a type column to this new table and my php classes would become useless.

Thanks in advance!


Solution:1

You're using a pattern called Polymorphic Associations, and no, there's no way to do that and use foreign keys to enforce referential integrity.

I suggest you make one common table that a_points, b_points, and c_points reference. Then your point pairs can reference that common table.

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

In other words, the way to make Polymorphic Associations work is to reverse the direction of the reference.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »