An order can contain several products, and a product can appear in several orders. Putting all that information in either record would make the data difficult to maintain. A many-to-many relationship instead connects the two through separate association records. section 10.2
Build the connection in stages:
- Give each order and product a unique identifier. section 10.2
- Create an Item record containing both identifiers. In a relational database, these become foreign keys to the Order and Product tables. section 10.2 section 10.3.2
- Keep information about the connection, such as the quantity ordered, on the Item record. section 10.6.1
class Address(db.Model):
__tablename__ = "address"
address_id = db.Column(db.Integer, primary_key=True)
street = db.Column(db.String)
zipcode = db.Column(db.String)
orders = db.relationship("Order", backref=db.backref("address"))
class Product(db.Model):
__tablename__ = "product"
product_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
class Item(db.Model): #1
__tablename__ = "item" #2
order_id = db.Column(db.Integer,
db.ForeignKey("order.order_id"),
primary_key=True) #3
product_id = db.Column(db.Integer,
db.ForeignKey("product.product_id"),
primary_key=True) #4
qty = db.Column(db.Integer) #5
order = db.relationship("Order",
backref=db.backref("items")) #6
product = db.relationship("Product") #7
The SQLAlchemy Item model makes the association explicit: its order and product identifiers are foreign keys and together form its primary key; qty belongs to the item. section 10.6.1
Following blog posts
A user can follow many posts, and a post can have many followers. Here, a user_post association table needs only the two foreign keys. SQLAlchemy’s secondary relationship exposes followed posts on a user and following users on a post. When someone comments, the application can add that user as a follower and notify the post’s followers. section 11.9
Use an association model when the connection carries data of its own, as an order item does. A simple association table suffices for the illustrated follower connection. Either way, the relationship lets the application work with related records without repeating the underlying order, product, user, or post data. section 10.6.1 section 11.9




