在同一列中引用外键 [英] Referencing foreign keys in the same column

问题描述

我正在为mysql设计一个bd-scheme。我的数据库存储3种点：a，b或c，路径由n对点组成：

Route = [（a1或b1或c1; a2或b2或c2），（a2或b2或c2; a3或b3或c3），...]

 创建表a_points（
 point_id serial not null，
 owner_id bigint unsigned not null，
 name varchar（20）not null，
 
主键（point_id），
外键（owner_id）引用othertable（other_id）
）engine = InnoDB; 
 
 
创建表b_points（
 point_id serial not null，
 owner_id bigint unsigned not null，
 name varchar（20）not null，
 fields varchar（20）not null，
 
 
主键（point_id），
外键（owner_id）引用othertable（owner_id）
 
） engine = InnoDB; 
 
创建表c_points（
 point_id serial not null，
 name varchar（20）not null，
 cfields varchar（20）not null，
 
主键（point_id）
）engine = InnoDB; 
 
创建表路径（
 path_id serial not null，
 name varchar（20）not null，
 
主键（path_id）
 ）engine = InnoDB; 
 
创建表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，
 
主键（pair_id），
外键（path_id）引用路径（path_id）
）engine = InnoDB; 
  

（*）一对点是（m，n）或从m到n

所以我正在存储一对点和他们的路径的id。我的问题是我不得不创建两列来标识表的名字m和n。 table_from为m和table_to为n。因此，我必须在代码中使用这两列来知道路由中保存的点（路径和point_pairs表）。我的问题：MySql是否提供了在同一列中引用n个外键的东西？我已经考虑加入一个b，c分表，但是我必须在这个新表中添加一个类型列，我的PHP类将变得无用。

谢谢提前！

解决方案

你正在使用一个名为多态关联的模式，不，没有办法做到这一点，使用外国

我建议您制作一个常见的表格， a_points ， b_points 和 c_points 参考。那么你的点对可以引用那个普通表。

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

换句话说，使多态关联工作的方法是反转引用的方向。 / p>

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!

解决方案

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.

这篇关于在同一列中引用外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！