liveBook · Manning

Many-to-many relationship

As explained in The Well-Grounded Python Developer: How the pros use Python and Flask

A many-to-many relationship is a data relationship in which multiple instances of one entity can be associated with multiple instances of another entity.

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

How The Well-Grounded Python Developer: How the pros use Python and Flask covers it

How deep it goes
Works from identifier-based CSV associations through database foreign keys to SQLAlchemy models and relationship attributes.
Examples
Uses order items with quantities and a blog’s post-follower table.
What it assumes
Builds from unique record identifiers and explains foreign keys before using Python model classes.
What you can do after
Apply it via SQLAlchemy to model order items or post followers and access their related records.
How it connects
Links association tables, foreign keys, one-to-many relationships, and object-relational mapping.

Also explained in

Compare how 5 books explain many-to-many relationship →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of The Well-Grounded Python Developer: How the pros use Python and Flask. The section links point to the parts of the book it draws on.