An order item connects an order to a product and records how many units of that product the order contains. It becomes useful when an order can contain multiple products: the quantity belongs to that particular order–product pairing, not to the product alone. section 10.2 section 10.6.1
Packing products, quantities, and shipping details into one CSV field calls for more delimiters and more parsing as orders grow. Separating customers, orders, addresses, products, and items follows the logical divisions in the data instead. An item supplies the link needed for the many-to-many relationship between orders and products. section 10.2 section 10.6.1
Representing an order with multiple products
Create an Item record for each product in the order. Each record identifies its order and product and stores a qty value. In the SQLAlchemy model, order_id and product_id are foreign keys that together form the primary key; an Order.items relationship provides access to the order’s items. 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
Inspect the Item model to see where the order–product link and quantity are stored. section 10.6.1
