Suppose a customer places several orders. Keeping a copy of the customer’s details in every order would repeat data. Instead, give each customer a unique identifier and put that identifier on each of their orders. One customer can then be linked to many orders; each order points to its customer. section 10.2 section 10.3.2
In a database, the identifier on the order is a foreign key: `order.customer_id` refers to `customer.customer_id`. Any number of orders can carry the same value. The same pattern fits a role held by many users, or a user who writes zero or more posts. Read it from either direction: one role has many users; each user has one role. section 10.3.2 section 9.6.1 section 11.1.1
Navigate from a user to their posts
A `Post` model stores `user_uid` as a foreign key to `User`. A SQLAlchemy `db.relationship("Post")` on `User` supplies a `posts` attribute: a Python list of that user’s zero or more posts, not a column in the user table. This lets application code work with related objects rather than manually match identifiers after every query. section 11.1.1 section 10.7.2
class Post(db.Model):
__tablename__ = "post"
post_uid = db.Column(db.String, primary_key=True, default=get_uuid)
parent_uid = db.Column(db.String,
db.ForeignKey("post.post_uid"),
default=None) #1
sort_key = db.Column(db.Integer,
nullable=False, unique=True,
default=get_next_sort_key) #2
user_uid = db.Column(db.String,
db.ForeignKey("user.user_uid"),
nullable=False, index=True)
title = db.Column(db.String)
content = db.Column(db.String)
children = db.relationship("Post",
backref=db.backref("parent",
remote_side=[post_uid], lazy="joined")) #3
active = db.Column(db.Boolean, nullable=False, default=True)
created = db.Column(db.DateTime,
nullable=False,
default=datetime.now(tz=timezone.utc))
updated = db.Column(db.DateTime, nullable=False, default=datetime.now(
tz=timezone.utc), onupdate=datetime.now(tz=timezone.utc))
A relationship can also connect rows of the same table. Here `parent_uid` points to another post, while SQLAlchemy supplies `children` and `parent` attributes for navigating posts and their comments. section 11.7.1
Use a one-to-many relationship when several records share one parent. If orders can contain multiple products *and* products can appear in multiple orders, that is many-to-many instead; an item association connects the two. Choosing the relationship first keeps the stored data structured and makes it easier to use from application code. section 10.2 section 10.3.2 section 10.9
