Establish many-to-many relationships

Question

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

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)) 

I mean, for example, an order, I need to have a connection with many for 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 this? In my example, I don't have db.relationship for bank_currency_identification , is this correct?

Answer 1

So, if I understand your question correctly, you want to reference the currencies_many table from the orders table. If so, you are right about the relationship of the foreign key to the currencies_many table.

However, on the way you may encounter some problems when you want to request orders from the banks table. I would suggest that, 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 more intuitive query interface.

 bank_orders = bank.orders 

It also makes your data model cleaner. It would be inconvenient to request orders from the staging table, which also contains the currency. Only my two cents, but with ease to understand the data model, is better than creating an uncomfortable relationship to preserve some redundancy.