I have many types of authentications, therefore I have one base postgres table (called "authentications"), and other types of authentications are derived from it. For example, an entity that gets authenticated using a password is called "password_authentications" and it inherits from the "authentications" table using postgres. The schema is thus:
CREATE TABLE authentications (
id
)
CREATE TABLE password_authentications (
id
password
) inherits (authentications)
This is very elegant, because I also have a table called "Users" that has a foreignkey into the authentications table:
CREATE TABLE users (
id
username
authentications_id
)
Hence a user can be authenticated by anything that inherits from authentications. So in future, if we need to authenticate users using RSA (for example), all I would do is make a base class from the authentications that described RSA authentication.
Now I want to use SQLAlchemy to build a mapping between Python classes and this structure. Specifically, I want to map a class called "User" to the table called "users" (that's easily done) but I want a relationship on password_authentication (not on authentication). In fact, I am looking to build this relationship dynamically, so if one user is authenticated using password, then SQLAlchemy will link the "Users" table to "Password_Authentications" table. If there is another type of auhentication, then User should be linked to that table instead. The only thing that all tables that User will link to have in common is that they are derived from Authentications table. Hence the "Users" table has a foreignkey into this table.
How can I accomplish the above? Is it even possible?
