python - What is the proper way to model this type of inheritance with SQLAlchemy? -

i have pair of postgresql tables (simplified example):

create table people (     id serial primary key,     created timestamp time zone default now() not null,     modified timestamp time zone null,     email varchar not null unique,     inactive boolean not null default false );  create table engineers (     id integer not null references people(id) on delete cascade on update cascade unique,     created timestamp time zone default now() not null,     modified timestamp time zone null,     login_name varchar not null unique,     primary key(id) ); 

engineers inherits people because "id" column people passes through engineers foreign key. column defined primary key in both tables. if wanted query login_name using someone's email address, done via sql:

select p.email engineers e    join people p on p.id = e.id    p.inactive = false , e.login_name = 'john.smith'; 

how model relationship using sqlalchemy's declarative style , perform similar query? seems "joined table inheritance" describes usage scenario, don't have discriminator column in tables. i've been trying use "concrete table inheritance" managing confuse myself.

i'd appreciate suggestions. thanks!

edited

the reason table structure way is because "person" might "engineer", "accountant", or "tester" (or combination of three). people table contains attributes common everyone, such name, phone, number, date of hire, etc.

the "created" , "modified" columns on engineers table not shared between people; 2 columns distinct each table. these aren't absolutely necessary, they're added default each table definition in database , used track changes audit/logging purposes.

what have here not either type because duplicate columns not others.

from docs:

sqlalchemy supports 3 forms of inheritance: single table inheritance, several types of classes represented single table, concrete table inheritance, each type of class represented independent tables, , joined table inheritance, class hierarchy broken among dependent tables, each class represented own table includes attributes local class.

because duplicate created , modified columns, candidate concrete inheritance. however, because expect employee incomplete entity without columns person, joined table inheritance.

i suggest try make fit joined table inheritance, i'm not entirely sure how mapper handle presence of duplicated columns. possible in joined table inheritance manipulate subclasses separately relations, i'm not sure how merged, if can merged, or mapper configuration available handle 1 used. if there way, this section show how.

however here started. polymorphic_on can sql expression (scroll down polymorphic_on argument)--it doesn't have column. if subclass engineers table, use exists subquery in discriminator.

below untested code not work without messing bit--it shows pattern you'll have use.

people_table = table('people', metadata,     column('id', integer, primary_key=true),     column('email', string, unique=true),     column('inactive', boolean, default=false), )  engineers_table = table('engineers', metadata,     column('id', integer, foreignkey('people.id'), primary_key=true),     column('engineer_info', string), )  class person(object):     pass  class engineer(person):     pass   discriminator = case(     [         (exists().where(people_table.c.id==engineers_table.c.id), 'engineer'),     ], else_='person')  mapper(person, people_table, polymorphic_identity='person', polymorphic_on=discriminator) mapper(engineer, engineer_table, polymorphic_identity='engineer') 

needless say, if it's @ possible should, own sanity:

1. add discriminator column person table.
2. get rid of created , modified columns in engineer table.