SQLAlchemy and Normalization

Question

I have a question about sqlalchemy and DB normalization.

I have a table called Accounts, and 2 kinds of persons, Natural_Persons and Legal_Persons.

I need to relate an account to just one person at a time.

For example, the account ID 4 is related with the Natural_Person ID 5.

But... How can I know when I query that information, if the ID 5 in the account record is from a Natural Person or a Legal one?

The simplest solution (for me at the moment) is to add a new field to the Accounts table called person_type, and to use, for example, a char to differentiate them.

So now I have a record in the accounts table with the following data:

account_id  = 4
person_id   = 5
person_type = N

But now I want to use the DB with sqlalchemy.

If I load an account record using a Account class instance then if I access the "person" attribute it should check the person_type field and create an instance of NaturalPerson class, or LegalPerson class according to the case!

Something like:

acc = Account(4)
acc.person

"""
if person_type == "L", person returns a LegalPerson instance
but otherwise ...
"""

Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Jul 07 '20 at 22:39
Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. PS The rearrangements in this question are not normalization. — philipxy, Jul 07 '20 at 22:41

score 4 · Answer 1 · answered Aug 29 '13 at 11:46

Table inheritance is what you are looking for:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine, Column, Integer, ForeignKey, String
Base = declarative_base()


class Account(Base):
    __tablename__ = 'account'
    id = Column(Integer, primary_key=True)
    person_id = Column(Integer, ForeignKey('person.id'))
    person = relationship("Person")


class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(20))

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


class NaturalPerson(Person):
    __mapper_args__ = {
        'polymorphic_identity':'natural'
    }


class LegalPerson(Person):
    __mapper_args__ = {
        'polymorphic_identity':'legal'
    }


engine = create_engine('sqlite:///:memory:', echo=True)

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

a = Account()
np = NaturalPerson()
a.person = np
session.add(a)

a = session.query(Account).first()
print type(a.person)

SQLAlchemy and Normalization

1 Answers1