SQLAlchemy variable relationship

Question

I'm unclear on how to configure SQLAlchemy for the following scenario:

• Several tables of varying nature.
• All tables need to reference a one-to-many notes table
• Would like to only have one notes table, servicing all these other tables, with the relationship based on a parent_type column in the notes table

For example: if relation_type is items, then relationship_id refers to the PK of the table_items table. If relation_type is orders then relationship_id would refer to the PK of table_orders

Answer 1

IMO, the problem you describe is best solved using the Generic Associations example.
The closest example of three examples listed is the discriminator_on_association.py

Answer 2

To answer your exact question (I think), you can place a relationship on each model that specifies a primaryjoin condition but you will not be able to manage the integrity of your foreign key relationship_id. Such a relationship on the Order model might look like this:

notes = relationship('Note', backref="order", primaryjoin="and_(Note.relationship_id == Order.id, relationship_type == 'orders')")

But in order to preserve the integrity of the foreign keys I would suggest taking one of two approaches. First you could use singe table inheritance and put all your fk's in the note table like:

id INT
relationship_type VARCHAR
item_id INT REFERENCES items.id
orders INT REFERENCES orders.id
objects INT REFERENCES objects.id

Then you can do something like the employees, managers, etc. example but for item_notes, order_notes and object_notes:

http://docs.sqlalchemy.org/en/rel_0_8/orm/inheritance.html#single-table-inheritance

A second approach is placing a foreign key in each of the tables that can have notes. The foreign key would point to a new table that represents a group of notes.

Specifically in this case you'd add a note_group_id column to the items, orders and objects tables. Then create a new table note_groups and create a note_group_id column in the notes table.

Then if needed the note_groups table could have a type column that refers to items, orders or objects. You could increase the accuracy of the one to one relationship by adding the type to the primaryjoin argument of the relationship(). The integrity of the relationship is not great but I think for simple notes it is good enough.

note_group = relationship('NoteGroup', uselist=False, backref="item", primaryjoin="and_(Item.note_group_id == NoteGroup.id, NoteGroup.type == 'items')")

For the second approach you would not need inheritance it would merely be a one to one relationship (from any table that can have notes to the note_groups table) and a one to many relationship from the note_groups table to the notes table.

There are even more approach but I think those are the two simplest. Hopefully other people have more suggestions.