I have been coming back to and given up on this several times.

Technically it’s flask-sqlalchemy if this makes a difference. I think what I am looking to do is make two many-to-one (or is it one-to-one?) relationships in the Pair table both referring to the Word table. A pair must have two words and a word can be in many pairs.

class Pair(db.Model):
    __tablename__ = "pairs"

    id = db.Column(db.Integer, primary_key=True)

    word_id = db.Column(db.Integer, db.ForeignKey('words.id'), nullable=False)
    partner_id = db.Column(
        db.Integer, db.ForeignKey('words.id'), nullable=False)

    word_sound = db.Column(db.String(), nullable=False)
    partner_sound = db.Column(db.String(), nullable=False)

    # These two relationships are where I'm particularly lost. The words 1 and 2 need to
    # refer to two separate sounds, so I can't just have two words and two sounds in any
    # order. Therefore I need to have two one-to-one links to the same table:

    word1t = db.relationship(
        "Word", foreign_keys=[word_id], primaryjoin="Pair.word_id==Word.id",, back_populates="pairs")
    word2t = db.relationship(
        "Word", foreign_keys=[partner_id], primaryjoin="Pair.partner_id==Word.id", back_populates="pairs")

class Word(db.Model):

    __tablename__ = "words"

    id = db.Column(db.Integer, primary_key=True)
    word = db.Column(db.String(), nullable=False)

    # Relationships

    # "partners" refers to all words that this word has a pair (link) with
    partners = db.relationship(
        primaryjoin=id == Pair.word_id,
        secondaryjoin=id == Pair.partner_id,
    # "pairs" is supposed to refer to all pairs that this word is a part of.
    pairs = db.relationship('Pair', primaryjoin=id ==
                        or_(Pair.word_id, Pair.partner_id))

I have these models. Basically I have words and (word-)pairs, where pairs consist of two words and also hold some information about the type of pair. In a pair I need two different words that each hold their own sound. This sound is defined by the word’s realationship to the other word, so fx in a pair of “pat” and “hat”, “pat” has “p” and “hat” has “h” because these sounds comprise the difference between the words. So what sound belongs with what word is important. But I can’t just put the sound in the Word table, because “pat” has a different sound (t) when linked to “pac” for example.

I have tried a number of things and deleted them again, but now I’m just hoping someone will show me the proper way to do it.

Help me, Stackoverflow. You’re my only hope.