When two kinds of records can relate to many records of the other kind, a join table keeps those connections explicit instead of forcing them into either main table. section 10.3.2 section 11.9
A join table, also called an association table, records the relationship with foreign keys to both related tables. Each row identifies one association; the related tables retain their own data. section 10.3.2 section 11.9
A practical join-table design typically includes:
- A foreign key pointing to the first table. section 10.3.2
- A foreign key pointing to the second table. section 10.3.2
- An ORM relationship that exposes associated objects without requiring a matching database column. section 11.9
- A guard against adding the same association twice when application code creates links. section 11.9
Following posts
A blog can let many users follow many posts. The user_post table stores user_uid and post_uid as foreign keys. SQLAlchemy then exposes posts_followed on User and users_following on Post, while application code adds a post only when the user is not already following it. section 11.9
