SQLAlchemy Foreign Key that can belong to one of several possible Models?

Question

I have a model Animals where the field locationID is a foreign key that can either be Forests.id or Zoos.id. Assume that Forests.id and Zoos.id are never the same.

Question: Is this something that can be done in SqlAlchemy?

Base = declarative_base()

class Forests(Base):
    __tablename__       = 'forests'
    id                  = Column(String(16), primary_key=True)

class Zoos(Base):
    __tablename__       = 'zoos'
    id                  = Column(String(16), primary_key=True)

class Animals(Base):
    __tablename__       = 'animals'
    name                = Column(String(16), primary_key=True)
    locationID          = Column(String(16), ForeignKey(Forests.id or Zoos.id)) # something like this...

---

More Details

Found something similar in an example in Sqlalchemy docs such as

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'employee',
        'polymorphic_on':type
    }

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'manager',
    }

Both Engineer and Manager have foreign keys in the employee table

However what I need is for Employee to have foreign key in either the engineer or the manager table, similar to the code below.

Code below is just for illustration purposes, it obviously wont work

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(50), ForeignKey('Engineer.id') or ForeignKey('Manager.id'))

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, primary_key=True)
    engineer_name = Column(String(30))

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, primary_key=True)
    manager_name = Column(String(30))

Answer 1

In this scenario, maybe we need to investigate our model a bit more, a good question to start with could be:

Do zoos and forests have something in common?

• animals live there
• GPS coordinates
• a name humans use to refer to them

Are there differences between them?

• zoos usually have entry fees
• zoos have enclosures
• forests have a type, possibly important for our animal dwellers

From the above, we could conclude that forests and zoos have a common base, since if we strip them down to the basics, they are for our purpose the same thing, e.g. they are a Dwelling for our Animal.

Building on the example from the docs, we could define this as such:

class Dwelling(Base):
    __tablename__ = 'dwelling'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'dwelling',
        'polymorphic_on':type
    }

class Forest(Dwelling):
    __tablename__ = 'forest'
    id = Column(Integer, ForeignKey('dwelling.id'), primary_key=True)
    biome = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'forest',
    }

class Zoo(Dwelling):
    __tablename__ = 'zoo'
    id = Column(Integer, ForeignKey('dwelling.id'), primary_key=True)
    fee = Column(Integer)

    __mapper_args__ = {
        'polymorphic_identity':'zoo',
    }

For our Animal, this would then look like this:

class Animal(Base):
    __tablename__ = 'animal'
    name = Column(String(16), primary_key=True)
    location = Column(Integer, ForeignKey(Dwelling.id))

Hope this helps.