PostgreSQL关系型数据库设计

本指南将介绍PostgreSQL中的各种数据关系类型,以及如何在FastAPI项目中使用SQLModel实现这些关系。

1. 关系型数据库基础

了解关系型数据库的基本概念和特点。

Name
特性
Type
features
Description
关系型数据库主要特性

关系型数据库特性

1. 数据组织形式:
• 以表格形式存储数据
• 表之间通过外键建立关联
• 支持复杂的关系查询

2. ACID特性:
• 原子性(Atomicity)
• 一致性(Consistency) 
• 隔离性(Isolation)
• 持久性(Durability)

3. 数据完整性:
• 实体完整性
• 参照完整性
• 用户定义完整性

2. 一对多关系

一对多是最常见的关系类型。

Name
示例
Type
example
Description
用户与文章的一对多关系

一对多关系示例

from typing import List, Optional
from sqlmodel import Field, SQLModel, Relationship

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    username: str
    # 一个用户可以有多篇文章
    posts: List["Post"] = Relationship(back_populates="author")

class Post(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    title: str
    content: str
    # 外键关联到用户
    author_id: Optional[int] = Field(default=None, foreign_key="user.id")
    # 每篇文章属于一个作者
    author: Optional[User] = Relationship(back_populates="posts")

# 使用示例
def get_user_posts(session: Session, user_id: int):
    user = session.get(User, user_id)
    return user.posts  # 获取用户的所有文章

3. 多对多关系

多对多关系需要中间表来实现。

Name
示例
Type
example
Description
课程与学生的多对多关系

多对多关系示例

# 中间表定义
class StudentCourseLink(SQLModel, table=True):
    student_id: Optional[int] = Field(
        default=None, 
        foreign_key="student.id", 
        primary_key=True
    )
    course_id: Optional[int] = Field(
        default=None, 
        foreign_key="course.id", 
        primary_key=True
    )

class Student(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    # 多对多关系
    courses: List["Course"] = Relationship(
        back_populates="students",
        link_model=StudentCourseLink
    )

class Course(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    # 多对多关系
    students: List[Student] = Relationship(
        back_populates="courses",
        link_model=StudentCourseLink
    )

# 使用示例
def enroll_student(session: Session, student_id: int, course_id: int):
    link = StudentCourseLink(student_id=student_id, course_id=course_id)
    session.add(link)
    session.commit()

4. 实际业务场景

常见业务场景中的关系设计。

Name
场景
Type
scenarios
Description
业务场景示例

业务场景示例

# 1. 电商系统
class Order(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    user_id: int = Field(foreign_key="user.id")
    # 一个订单包含多个商品
    items: List["OrderItem"] = Relationship(back_populates="order")
    
class OrderItem(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    order_id: int = Field(foreign_key="order.id")
    product_id: int = Field(foreign_key="product.id")
    quantity: int
    
# 2. 社交系统
class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    # 用户发布的帖子(一对多)
    posts: List["Post"] = Relationship(back_populates="author")
    # 关注的用户(多对多)
    following: List["User"] = Relationship(
        back_populates="followers",
        link_model=UserFollows
    )
    # 粉丝(多对多)
    followers: List["User"] = Relationship(
        back_populates="following",
        link_model=UserFollows
    )
    
# 3. 博客系统
class Article(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    author_id: int = Field(foreign_key="user.id")
    # 文章的评论(一对多)
    comments: List["Comment"] = Relationship(back_populates="article")
    # 文章的标签(多对多)
    tags: List["Tag"] = Relationship(
        back_populates="articles",
        link_model=ArticleTag
    )