Backend Models Standards

Tables: Plural, snake_case (users, order_items, payment_methods)

Relationships: Descriptive and clear

• user.orders (one-to-many)
• order.items (one-to-many)
• product.categories (many-to-many)

Avoid generic names: data, info, record, entity

Required Fields

Timestamps on every model:

created_at = Column(DateTime, nullable=False, default=datetime.utcnow)
updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow)

Primary keys: Always explicit, prefer UUIDs for distributed systems or auto-incrementing integers for simplicity

Why: Auditing, debugging, data lineage tracking, soft deletes

Data Integrity - Database Level

Use constraints, not just application validation:

# NOT NULL for required fields
email = Column(String(255), nullable=False)

# UNIQUE constraints
email = Column(String(255), unique=True, nullable=False)

# CHECK constraints for business rules
age = Column(Integer, CheckConstraint('age >= 18'))

# Foreign keys with explicit cascade behavior
user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'))

Why: Database enforces rules even if application code bypassed. Defense in depth.

Data Types - Choose Appropriately

Why: Correct types enable database optimizations, constraints, and prevent data corruption.

Indexes - Performance Critical

Always index:

• Primary keys (automatic)
• Foreign keys (manual in most ORMs)
• Columns in WHERE clauses
• Columns in JOIN conditions
• Columns in ORDER BY clauses

Example:

class Order(Base):
    __tablename__ = 'orders'
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'), index=True)
    status = Column(String(50), index=True)  # Frequently filtered
    created_at = Column(DateTime, index=True)  # Frequently sorted

Don't over-index: Each index slows writes. Index only queried columns.

Relationships - Explicit Configuration

Define both sides of relationships:

# One-to-many
class User(Base):
    orders = relationship('Order', back_populates='user', cascade='all, delete-orphan')

class Order(Base):
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship('User', back_populates='orders')

Cascade behaviors:

• CASCADE: Delete related records (user deleted → orders deleted)
• SET NULL: Nullify foreign key (category deleted → product.category_id = NULL)
• RESTRICT: Prevent deletion if related records exist
• NO ACTION: Database default, usually same as RESTRICT

Choose based on business logic, not convenience.

Validation - Two Layers

Model-level validation (application):

@validates('email')
def validate_email(self, key, email):
    if not re.match(r'^[^@]+@[^@]+\.[^@]+$', email):
        raise ValueError('Invalid email format')
    return email

Database-level constraints (see Data Integrity section)

Why both: Model validation provides clear error messages. Database constraints prevent data corruption if application bypassed.

What Belongs in Models

YES:

• Field definitions and types
• Relationships to other models
• Simple property methods (@property def full_name)
• Data validation rules
• Database constraints

NO:

• Business logic (move to service layer)
• External API calls
• Complex calculations (move to service methods)
• Email sending, file uploads, etc.

Models represent data structure, not behavior.

Normalization vs Performance

Normalize when:

• Data has clear entity boundaries
• Updates need to propagate (user email changes once)
• Avoiding data duplication is critical

Denormalize when:

• Read performance critical (analytics, reporting)
• Data rarely changes (historical snapshots)
• Joins become too expensive

Default to normalized. Denormalize only with evidence of performance issues.

Common Patterns

Soft deletes:

deleted_at = Column(DateTime, nullable=True, index=True)

# Query only active records
query = session.query(User).filter(User.deleted_at.is_(None))

Polymorphic associations:

# Avoid if possible - complex and hard to maintain
# Prefer separate relationship fields or inheritance

Enums for fixed values:

from enum import Enum

class OrderStatus(str, Enum):
    PENDING = 'pending'
    PAID = 'paid'
    SHIPPED = 'shipped'
    DELIVERED = 'delivered'

status = Column(Enum(OrderStatus), nullable=False, default=OrderStatus.PENDING)

Testing Models

Test constraints and validation:

def test_user_email_required():
    with pytest.raises(IntegrityError):
        user = User(name='Test')
        session.add(user)
        session.commit()

def test_user_email_unique():
    user1 = User(email='[email protected]')
    user2 = User(email='[email protected]')
    session.add(user1)
    session.commit()
    
    with pytest.raises(IntegrityError):
        session.add(user2)
        session.commit()

Test relationships:

def test_user_orders_cascade_delete():
    user = User(email='[email protected]')
    order = Order(user=user)
    session.add(user)
    session.commit()
    
    session.delete(user)
    session.commit()
    
    assert session.query(Order).count() == 0

Checklist for New Models

• Singular model name, plural table name
• Primary key defined
• created_at and updated_at timestamps
• NOT NULL on required fields
• UNIQUE constraints where appropriate
• Foreign keys with explicit cascade behavior
• Indexes on foreign keys and queried columns
• Appropriate data types (not all VARCHAR)
• Validation at model and database levels
• Relationships defined on both sides
• Tests for constraints and validation