Set relation to many-to-many relationship

Question

I have this many-to-many relationship that works correctly. However, now I need to have another class with a relation to this many-to-many.

currencies = db.Table('currencies_many',
    db.Column('id', db.Integer, primary_key=True),
    db.Column('currency_id', db.Integer, db.ForeignKey('currencies.id')),
    db.Column('bank_id', db.Integer, db.ForeignKey('banks.id'))
)

class Bank(db.Model):
    __tablename__ = 'banks'
    id = db.Column(db.Integer, primary_key=True)
    bank_name = db.Column(db.String(300))
    currencies = db.relationship('Currency', secondary=currencies,
        backref=db.backref('banks', lazy='dynamic'))

class Currency(db.Model):
    __tablename__ = 'currencies'
    id = db.Column(db.Integer, primary_key=True)
    currency_name = db.Column(db.String(300))

What I mean is, for example, an order, I need to have the association to many to many.

class Order(db.Model):
    __tablename__ = 'orders'
    id = db.Column(db.Integer, primary_key=True)
    bank_currency_identification = db.Column(db.Integer, db.ForeignKey('currencies_many.id'))

How can I do that? In my example I don't have db.relationship for bank_currency_identification, it is correct?

Answer 1

So if I understand your question correctly, you want to reference the currencies_many table from your orders table. If so, you are correct in having a foreign key relationship with the currencies_many table.

However, down the road you may come into some trouble when you want to query orders from your banks table. I would suggest, although it seems redundant, to create a one-to-many relationship between Order and Bank as well as between Order and Currency.

bank_id = db.Column(db.Integer, db.ForeignKey('bank.id'))
currency_id = db.Column(db.Integer, db.ForeignKey('currency.id'))

And then in the Bank class

orders = db.relationship('Order', backref='bank')

This gives you a much cleaner querying interface.

bank_orders = bank.orders

As well as makes your data model cleaner. It would be awkward to have to query orders from an intermediate table that also houses the currency. Just my two cents, but having an easy to understand Data model is better than making awkward relationships to save some redundancy.