在外键循环依赖：使用它还是避免它？ [英] Circular dependencies in foreign keys: use it or avoid it?

问题描述

我的应用程序将大量来自数据库的数据加载到复杂的数据结构中。内存中的数据结构重新组合数据库的结构，这意味着如果数据库包含以下表：

• 表A，键是表B，键是B1，其中一列是表A的键[
  
• 表C的键的外键，键是C1，其中一列是[B]键的外键

然后我有类A，B和C，以及：B（B :: m_a）的一个数据成员是指向A的指针

$ b

• b
• C（C :: m_b）的一个数据成员是指向B的指针
  
  
  

  这意味着如果我加载数据库，我必须以正确的顺序加载它。如果我第一次加载C，那么它会抱怨它不能设置值C :: m_b，因为它应该指向的实例没有加载。
  
  

  问题是什么时候A中还有一列是其他表中的一个外键，假设C。

  
  
  

  我可以通过加载所有外键作为字符串来解决这个问题，然后在加载所有数据后执行查找，但由于我有时需要加载数百万条记录，所以我不能在这些（尽管是临时的）字符串上花费内存。
  $阅读好的设计（例如，大规模C ++软件设计一书），在我看来，有一个循环引用是一个坏主意。
  E.g.如果文件X.H包含Y.H，但是Y.H也包含X.H，则可能是设计不好;如果X类依赖于Y类，反之亦然，则可能有一个糟糕的设计，应该通过提取这个依赖关系并引入一个依赖于X和Y的第三类Z来解决（X和Y将不再依赖于对方） 。
  
  

  将这个设计规则扩展到数据库设计是个好主意吗？换句话说：防止外键循环引用

  
  

  解决方案

  要创建时，你应该需要一个循环引用的唯一时间是
  
  

   表雇员
  雇员ID< ------- --- | 
   SupervisorEmployeeID --- | 
    

  
  

  My application loads lots of data from a database into a complex data structure. The in-memory data structure ressembles the structure of the database, which means that if the database contains the following tables:

  • table A, key is A1
  • table B, key is B1, one of the columns is a foreign key to [the key of] table A
  • table C, key is C1, one of the columns is a foreign key to [the key of] table B

  Then I have classes A, B and C, and:

  • a data member of B (B::m_a) is a pointer to A
  • a data member of C (C::m_b) is a pointer to B

  This implies that if I load the database, that I have to load it in the correct order. If I first load C, then it will complain that it cannot set the value C::m_b because the instance where it should point to was not loaded.

  Problem is when there is also a column in A that is a foreign key to one of the other tables, let's say C.

  I could solve the problem by loading all foreign keys as strings, and then perform a lookup after all the data has been loaded, but since I sometimes have to load millions of records, I can't afford to spend memory on these (albeit temporary) strings.

  Having read about good design (e.g. the book "Large Scale C++ Software Design") it seems to me that it's a bad idea to have circular references at all. E.g. if file X.H includes Y.H, but Y.H also includes X.H you probably have a bad design; if class X depends on class Y and vice versa you probably have a bad design, which should be solved by extracting this dependency and introducing a third class Z, which depends on X and Y (X and Y won't depend on eachother anymore).

  Is it a good idea to also extend this design-rule to database design? In other words: preventing circular references in foreign keys.

  解决方案

  The only time you should need a circular reference is when you are creating a hierarchical structure, such as an organizational tree.

  Table Employees
     EmployeeID   <----------|
     SupervisorEmployeeID ---|
  

  这篇关于在外键循环依赖：使用它还是避免它？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！