SQL field with an foreign id from two tables

Question

I am constructing a postgressql database with sqlalchemy. I was wondering if it is possible to have a structure like this:

Two tables consisting of persons, and organisations.

Table - Person - 
person_id, name
1,         Jeffery
2,         Frank

Table - Org -
org_id, name
a,      Pepsi
b,      Microsoft

A third table of addresses or either persons or organisations:

Table - Addresses -
address_id, type_of_id, (either person_id or org_id), addresses
1,          person,     1, "2 Jeffery way"
2,          person,     1, "7 Holland Avenue"
3,          org,        b, "1 Microsoft way"
4,          person,     2, "2 Frank Street"

Is this possible with postgres? And how do I write it into sqlalchemy python code?

Update:

I believe this is called a Polymorphic association as in: Possible to do a MySQL foreign key to one of two possible tables?

Answer 1

Your question consists out of two parts:

1. Create a union of the persons and organisations table.
2. Add the type of entity (person/organisation) to the table.

The first part can be done using union_all. More information on this can be found at:

• How to concatenate unrelated queries in SqlAlchemy
• http://docs.sqlalchemy.org/en/latest/orm/query.html

the second part, adding the type of entity (person/organisation) can be achieved using literal_column. For more information on this:

• How do I select literal values in an sqlalchemy query? (see comment in regards to quoting the text when using postgresql)

The following is a short example combining these elements:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


## Define sample models


class Person(Base):
    __tablename__ = 'person'
    person_id = Column('id', Integer, primary_key=True)
    name = Column('name', String(250))

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return '[{person_id}] - {name}'.format(person_id=self.person_id, name=self.name)


class Orqanisation(Base):
    __tablename__ = 'organisation'
    organisation_id = Column('id', Integer, primary_key=True)
    name = Column('name', String(250))

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return '[{organisation_id}] - {name}'.format(organisation_id=self.organisation_id, name=self.name)


engine = create_engine('sqlite:///')
session = sessionmaker()
session.configure(bind=engine)
ex_ses = session()
Base.metadata.create_all(engine)

## Create sample data

# http://listofrandomnames.com/ for something else then A,B,...
names = ['Virgil', 'Ina', 'Oleta', 'Suzette', 'Usha', 'Ilda', 'Lorean', 'Cinthia', 'Sheba', 'Waneta', 'Donnette']

organisations = ['stackoverflow','Cross validated','Meta stack overflow','Area 51']

# Create persons
for name in names:
    ex_ses.add(Person(name=name))

# Create organisations
for org in organisations:
    ex_ses.add(Orqanisation(name=org))

ex_ses.commit()

# queries
print('Persons:')
for person in ex_ses.query(Person).all():
    print('* ',person)

'''
Persons:
*  [1] - Virgil
*  [2] - Ina
*  [3] - Oleta
*  [4] - Suzette
*  [5] - Usha
*  [6] - Ilda
*  [7] - Lorean
*  [8] - Cinthia
*  [9] - Sheba
*  [10] - Waneta
*  [11] - Donnette
'''

print('Organisations:')
for org in ex_ses.query(Orqanisation).all():
    print('* ',org)

'''
Organisations:
*  [1] - stackoverflow
*  [2] - Cross validated
*  [3] - Meta stack overflow
*  [4] - Area 51
'''

# Query combining tables, adding type of entity

from sqlalchemy.sql.expression import literal_column

persons = ex_ses.query(Person.person_id, Person.name,literal_column("'person'").label('type'))
organisations = ex_ses.query(Orqanisation.organisation_id, Orqanisation.name,literal_column("'Organisation'").label('type'))

print('Persons - Organisations:')
for pers_org in persons.union_all(organisations).all():
    print('* {id} - {name} (type: {type})'.format(id=pers_org[0],name=pers_org[1],type=pers_org[2]))

'''
Persons - Organisations:
* 1 - Virgil (type: person)
* 2 - Ina (type: person)
* 3 - Oleta (type: person)
* 4 - Suzette (type: person)
* 5 - Usha (type: person)
* 6 - Ilda (type: person)
* 7 - Lorean (type: person)
* 8 - Cinthia (type: person)
* 9 - Sheba (type: person)
* 10 - Waneta (type: person)
* 11 - Donnette (type: person)
* 1 - stackoverflow (type: Organisation)
* 2 - Cross validated (type: Organisation)
* 3 - Meta stack overflow (type: Organisation)
* 4 - Area 51 (type: Organisation)
'''

Answer 2

Adapting from: http://www.duanqu.tech/questions/2898814/sqlalchemy-polymorphic-association and http://docs.sqlalchemy.org/en/latest/orm/inheritance.html

from sqlalchemy.ext.declarative import as_declarative, declared_attr
from sqlalchemy import create_engine, Integer, Column, \
    String, ForeignKey
from sqlalchemy.orm import Session, relationship
from sqlalchemy_utils import database_exists, create_database
from sqlalchemy.dialects.postgresql import UUID
import uuid


@as_declarative()
class Base(object):
    # Set the tablenames
    # to the class names
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()
    # give an id to each class
    id = Column(UUID, default=lambda: str(uuid.uuid4()), primary_key=True)
# Base = declarative_base()


class EntityInterface(Base):
    discriminator = Column(String)

    __mapper_args__ = {"polymorphic_on": discriminator}


class Address(Base):
    street = Column(String)
    city = Column(String)
    zip = Column(String)
    entity_id = Column(UUID, ForeignKey(EntityInterface.id),
                       default=lambda: str(uuid.uuid4()))
    entity = relationship(EntityInterface)

    def __repr__(self):
        return ("%s(street=%r, city=%r, zip=%r, company=%r)" %
                (self.__class__.__name__, self.street, self.city,
                 self.zip, self.entity))


class Person(EntityInterface):
    id = Column(UUID, ForeignKey(EntityInterface.id),
                default=lambda: str(uuid.uuid4()), primary_key=True)
    name = Column(String)
    __mapper_args__ = {"polymorphic_identity": "Person"}


class Organization(EntityInterface):
    id = Column(UUID, ForeignKey(EntityInterface.id),
                default=lambda: str(uuid.uuid4()), primary_key=True)
    name = Column(String)
    __mapper_args__ = {"polymorphic_identity": "Organization"}


engine = create_engine(
    'postgresql://paul_tsc:paul123@localhost/' + "poly_testing",
    echo=False)

# if it doesn't exist, create it
if not database_exists(engine.url):
    create_database(engine.url)

Base.metadata.create_all(engine)
session = Session(engine)

address1 = Address(street='test-1', city="Detroit", zip="56785")
address2 = Address(street='test-2', city="Phoenix", zip="110322")
address3 = Address(street='test-3', city="Washington", zip="432414")

org1 = Organization(name="Org-1 TEST")
org2 = Organization(name="Org-2 TEST")
person1 = Person(name="Person-1 TEST")
person2 = Person(name="Person-2 TEST")

address1.entity = org1
address2.entity = person1
address3.entity = person1

session.add_all([address1, address2, address3])

session.commit()

address3.entity = org2

session.commit()

print("PRINTING, TOTAL = %s" % session.query(Address).count())
for address in session.query(Address):
    print("ADDRESS = %s" % address)

Answer 3

I see two ways to model this in a relational database:

1. Relationship table

   CREATE TABLE address_assignment (
      person_id integer REFERENCES person,
      org_id text REFERENCES org,
      address_id integer NOT NULL REFERENCES address,
      CHECK (person_id IS     NULL AND org_id IS NOT NULL
          OR person_id IS NOT NULL AND org_id IS     NULL),
      UNIQUE (person_id, org_id, address_id)
   );
   

   This model would be particularly useful if different entities can share one address.

2. “Poor man's superclass”

   Create a table that implements a superclass for person and organization, maybe containing all common attributes:

   CREATE TABLE located_entity (
      locent_id INTEGER PRIMARY KEY,
      name text NOT NULL
   );
   

   Both person and org get a foreign key to located_entity, and you can implement the relationship with address by also adding a foreign key to located_entity to address.

   If you usually look up persons by their address rather than addresses by person, you could consider adding the foreign keys between located_entity and person or org person in the other direction, with a similar check constraint as in solution 1.