Many-to-Many Relationships

A many-to-many relationship means:

• many A relate to many B

Example:

• a Post can have many Tags
• a Tag can apply to many Posts

diagram many-to-many needs a third table mermaid

  erDiagram
  POST ||--o{ POST_TAGS : "appears in"
  TAG  ||--o{ POST_TAGS : "appears in"
  POST {
    int id PK
    string title
  }
  TAG {
    int id PK
    string name
  }
  POST_TAGS {
    int post_id FK
    int tag_id FK
  }

There is no way to put a foreign key on either side: an author could have many tags and a tag could belong to many authors. The association table holds one row per pairing, and relationship(secondary=...) is what lets you ignore it in normal use.

Association table

python

post_tags = db.Table(
    "post_tags",
    db.Column("post_id", db.Integer, db.ForeignKey("posts.id"), primary_key=True),
    db.Column("tag_id", db.Integer, db.ForeignKey("tags.id"), primary_key=True),
)

Models

python

class Post(db.Model):
    __tablename__ = "posts"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
 
    tags = db.relationship("Tag", secondary=post_tags, back_populates="posts")
 
 
class Tag(db.Model):
    __tablename__ = "tags"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True, nullable=False)
 
    posts = db.relationship("Post", secondary=post_tags, back_populates="tags")

Using it

python

post = Post(title="Flask Tips")
tag = Tag(name="flask")
post.tags.append(tag)
 
db.session.add(post)
db.session.commit()

When you need an association model

If the relationship needs extra fields (e.g., created_at, role), use an association model instead of db.Table.