0

I need to map out a design between 3 tables.

Eventually it needs to be compatible with PostgreSQL and if someone gives me clues for an implementation with SQLAlchemy it would be just perfect.

Consider a Foo table and a Bar table. There is a classic relationship one to many between a Foo and a Bar, respecting SQLAlchemy's standards: 

class Foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    bars = relationship("Bar", back_populates="foo")

class Bar(Base):
    __tablename__ = 'bar'
    id = Column(Integer, primary_key=True)
    foo_id = Column(Integer, ForeignKey('foo.id'))
    foo = relationship("Foo", back_populates="bars")

I need to implement a table Magic that is linked to either one single Foo OR one single Bar.

The table Magic would contain one field foo_id and one field bar_id:

class Magic(Base):
    __tablename__ = 'magic'
    id = Column(Integer, primary_key=True)
    # [...]
    foo_id = Column(Integer, ForeignKey('foo.id'))
    foo = relationship("Foo", back_populates="magic")
    bar_id = Column(Integer, ForeignKey('bar.id'))
    bar = relationship("Bar", back_populates="magic")

But I need to somehow restrict the fact that if one is set, the other must be null.

And I am afraid that this design will complexify the logic of my RESTFull API endpoints.

Any ideas?

SivolcC
  • 3,258
  • 2
  • 14
  • 32
  • The Parent and Child tables have a parent-child relationship. Obviously. But do they also have a class-subclass relationship? – Walter Mitty Aug 29 '18 at 07:08
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Aug 29 '18 at 08:48
  • 1
    You appear to maybe be suggesting the multiple/many/2 FKs to multiple/many/2 tables anti-pattern for sql/database subtyping/inheritance. But you are not very clear about your requirements or your design. It's not even clear whether you are using "relationship" in the sense of association/relation/table or FK. PS Why do you have both a child & parent columns for an entity? This is likely redundant. X is the parent of Y when Y is the child of X. – philipxy Aug 29 '18 at 08:50

0 Answers0