How can I attach generic `Contact` to multiple different tables?

Question

I'm trying to create a set of tables which can all have a Contactassigned to them.

class Contact(Base):
    __tablename__ = "contact"
    id = Column(Integer, primary_key=True)
    name = Column(String, index=True, nullable=False, doc="Name of the contact.")
    phone = Column(String, index=True, doc="Phone number of the contact.")

Contacts can be linked to from various other tables, and one record can have more than one contact in different fields.

class BusinessGroup(Base):
    __tablename__ = "business_group"
    id = Column(Integer, primary_key=True)
    name = Column(String, index=True, nullable=False, doc="Name of the group.")
    main_contact = Column(Integer, ForeignKey("contact.id"), doc="Main contact details for the group.")
    second_contact = Column(Integer, ForeignKey("contact.id"), doc="Second contact details for the group.")

class Vendor(Base):
    __tablename__ = "vendor"
    id = Column(Integer, primary_key=True)
    name = Column(String, index=True, nullable=False, doc="Name of the vendor.")
    contact = Column(Integer, ForeignKey("contact.id"), doc="Main contact details for the vendor.")

This setup seems to work, but in flask-admin, no contact fields show up when creating a new item for either BusinessGroup or Vendor.

How can I make this design work? Or should I be modelling this kind of relationship in a different way entirely?

Answer 1

I ended up subclassing the Contact table so that I could have:

class MainContact(Contact):
    __tablename__ = "main_contact"
    id = Column(Integer, ForeignKey("contact.id"), primary_key=True)
    business_group = relationship("BusinessGroup", back_populates="main_contact")
    business_group_id = Column(Integer, ForeignKey("business_group.id"), nullable=False)
    ...

class SecondContact(Contact):
    __tablename__ = "second_contact"
    id = Column(Integer, ForeignKey("contact.id"), primary_key=True)
    business_group = relationship("BusinessGroup", back_populates="second_contact")
    business_group_id = Column(Integer, ForeignKey("business_group.id"), nullable=False)
    ...

class BusinessGroup(Base):
    __tablename__ = "business_group"
    id = Column(Integer, primary_key=True)
    name = Column(String, index=True, nullable=False, doc="Name of the group.")
    main_contact = relationship(
        "MainContact", back_populates="business_group", uselist=False, 
        doc="Main contact details for the business_group."
    )
    second_contact = relationship(
        "SecondContact", back_populates="business_group", uselist=False, 
        doc="Second contact details for the business_group."
    )

As well as requiring subclassing so that we can have two different contacts referred to from the same model, the other important part was adding the foreign key relationships, so that the contacts show up in the Flask Admin panel.