title: sqlalchemy and handling multiple foreign keys in a single column
body: <p>I'm kind of a noobie when it comes to sqlalchemy and RDBMS's but I've recently taken on a project that requires it. I want to try and do everything through the ORM too. This is the set up that I have:</p>
  
  <pre><code>class A(Base):
      __tablename__ = 'a'
  
      id = Column(Integer, primary_key=True)
  
      b_children = relationship('B')
      x_children = relationship('X')
      y_children = relationship('Y')
  
  class B(Base):
      __tablename__ = 'b'
  
      id = Column(Integer, primary_key=True)
  
      c_children = relationship('C')
      x_children = relationship('X')
      y_children = relationship('Y')
  
      a_parent_id = Column(Integer, ForeignKey('a.id'))
  
  class C(Base):
      __tablename__ = 'c'
  
      id = Column(Integer, primary_key=True)
  
      x_children = relationship('X')
      y_children = relationship('Y')
  
      b_parent_id = Column(Integer, ForeignKey('b.id'))
  
  class X(Base):
      __tablename__ = 'x'
  
      id = Column(Integer, primary_key=True)
  
      a_parent_id = Column(Integer, ForeignKey('a.id'))
      b_parent_id = Column(Integer, ForeignKey('b.id'))
      c_parent_id = Column(Integer, ForeignKey('c.id'))
  
  class Y(Base):
      __tablename__ = 'y'
  
      id = Column(Integer, primary_key=True)
  
      a_parent_id = Column(Integer, ForeignKey('a.id'))
      b_parent_id = Column(Integer, ForeignKey('b.id'))
      c_parent_id = Column(Integer, ForeignKey('c.id'))
  </code></pre>
  
  <p>So A, B, and C are all hierarchical. A's contains B's, B's contain C's, and C's are the end of that tree. A's, B's, and C's can all contain X's and Y's. So far so good, right? The kicker is that X's and Y's can only be attached to one A, B, or C at a time. I don't want to have an A and a B that reference the same X or Y. I also don't want to have excess columns that aren't being used. In this case, X's and Y's will always have two empty columns.</p>
  
  <p>I am pretty sure that there is no way for sqlalchemy (or SQL for that matter) to combine multiple ForeignKeys into a single column, because that wouldn't make much sense. My question, however, is this: Is what I want to achieve here even possible? Is there a better way to do this using a different database system? Or should I completely rethink my database design?</p>