Models and Databases

Composite Primary Keys

Composite primary keys (also called compound keys) consist of multiple columns that together uniquely identify a record. While Django traditionally uses single-column auto-incrementing primary keys, Django 4.2+ provides experimental support for composite primary keys.

Composite Primary Keys

Understanding Composite Primary Keys

A composite primary key uses multiple fields to uniquely identify a model instance, which is common in legacy databases and certain data modeling scenarios.

Traditional Django Approach

# Django's default: single auto-incrementing primary key
class Article(models.Model):
    # id field is automatically created
    title = models.CharField(max_length=200)
    author = models.ForeignKey(User, on_delete=models.CASCADE)
    
    class Meta:
        # Use unique_together for compound uniqueness
        unique_together = [['title', 'author']]

Composite Primary Key Support (Django 4.2+)

# Experimental composite primary key support
class BookEdition(models.Model):
    isbn = models.CharField(max_length=13)
    edition_number = models.IntegerField()
    title = models.CharField(max_length=200)
    publication_date = models.DateField()
    
    class Meta:
        # Define composite primary key
        constraints = [
            models.UniqueConstraint(
                fields=['isbn', 'edition_number'],
                name='book_edition_pk'
            )
        ]
        # Note: Full support is experimental in Django 4.2

Working with Composite Keys

Legacy Database Integration

When working with existing databases that use composite keys:

# models.py - Mapping to legacy database
class LegacyOrder(models.Model):
    order_id = models.IntegerField()
    order_date = models.DateField()
    customer_code = models.CharField(max_length=10)
    product_code = models.CharField(max_length=20)
    quantity = models.IntegerField()
    
    class Meta:
        db_table = 'legacy_orders'
        managed = False  # Don't let Django manage this table
        unique_together = [['order_id', 'order_date', 'customer_code']]
        
    def __str__(self):
        return f"Order {self.order_id} - {self.customer_code}"

# Querying with composite keys
order = LegacyOrder.objects.get(
    order_id=12345,
    order_date='2023-01-01',
    customer_code='CUST001'
)

Natural Keys as Alternative

Natural keys provide a Django-friendly alternative to composite primary keys:

from django.db import models

class BookManager(models.Manager):
    def get_by_natural_key(self, isbn, edition):
        return self.get(isbn=isbn, edition_number=edition)

class Book(models.Model):
    # Still use Django's auto PK internally
    isbn = models.CharField(max_length=13)
    edition_number = models.IntegerField()
    title = models.CharField(max_length=200)
    
    objects = BookManager()
    
    class Meta:
        unique_together = [['isbn', 'edition_number']]
    
    def natural_key(self):
        return (self.isbn, self.edition_number)
    
    def __str__(self):
        return f"{self.title} (ISBN: {self.isbn}, Ed. {self.edition_number})"

# Usage
book = Book.objects.get_by_natural_key('978-0-123456-78-9', 2)

Patterns for Composite Key Scenarios

Many-to-Many Through Models

class Student(models.Model):
    student_id = models.CharField(max_length=10, unique=True)
    name = models.CharField(max_length=100)

class Course(models.Model):
    course_code = models.CharField(max_length=10, unique=True)
    title = models.CharField(max_length=200)

class Enrollment(models.Model):
    student = models.ForeignKey(Student, on_delete=models.CASCADE)
    course = models.ForeignKey(Course, on_delete=models.CASCADE)
    semester = models.CharField(max_length=20)
    grade = models.CharField(max_length=2, blank=True)
    enrollment_date = models.DateField(auto_now_add=True)
    
    class Meta:
        # Composite uniqueness: student can only enroll once per course per semester
        unique_together = [['student', 'course', 'semester']]
        indexes = [
            models.Index(fields=['student', 'semester']),
            models.Index(fields=['course', 'semester']),
        ]
    
    def __str__(self):
        return f"{self.student.name} - {self.course.title} ({self.semester})"

# Querying
enrollment = Enrollment.objects.get(
    student__student_id='S12345',
    course__course_code='CS101',
    semester='Fall 2023'
)

Time-Series Data with Composite Keys

class SensorReading(models.Model):
    sensor_id = models.CharField(max_length=50)
    timestamp = models.DateTimeField()
    temperature = models.DecimalField(max_digits=5, decimal_places=2)
    humidity = models.DecimalField(max_digits=5, decimal_places=2)
    
    class Meta:
        # Each sensor can only have one reading per timestamp
        unique_together = [['sensor_id', 'timestamp']]
        indexes = [
            models.Index(fields=['sensor_id', 'timestamp']),
            models.Index(fields=['timestamp']),  # For time-range queries
        ]
        ordering = ['sensor_id', '-timestamp']
    
    def __str__(self):
        return f"Sensor {self.sensor_id} at {self.timestamp}"

# Efficient querying
from django.db.models import Q
from datetime import datetime, timedelta

# Get latest reading for each sensor
latest_readings = SensorReading.objects.filter(
    timestamp__gte=datetime.now() - timedelta(hours=1)
).order_by('sensor_id', '-timestamp').distinct('sensor_id')

# Get specific reading
reading = SensorReading.objects.get(
    sensor_id='TEMP_001',
    timestamp='2023-01-01 12:00:00'
)

Versioned Data Models

class DocumentVersion(models.Model):
    document_id = models.CharField(max_length=50)
    version_number = models.IntegerField()
    content = models.TextField()
    author = models.ForeignKey(User, on_delete=models.CASCADE)
    created_at = models.DateTimeField(auto_now_add=True)
    is_current = models.BooleanField(default=True)
    
    class Meta:
        unique_together = [['document_id', 'version_number']]
        indexes = [
            models.Index(fields=['document_id', '-version_number']),
            models.Index(fields=['document_id', 'is_current']),
        ]
    
    def save(self, *args, **kwargs):
        if self.is_current:
            # Mark all other versions as not current
            DocumentVersion.objects.filter(
                document_id=self.document_id,
                is_current=True
            ).update(is_current=False)
        super().save(*args, **kwargs)
    
    @classmethod
    def get_current_version(cls, document_id):
        return cls.objects.get(document_id=document_id, is_current=True)
    
    @classmethod
    def get_version(cls, document_id, version_number):
        return cls.objects.get(
            document_id=document_id,
            version_number=version_number
        )

# Usage
current = DocumentVersion.get_current_version('DOC_001')
specific = DocumentVersion.get_version('DOC_001', 5)

Custom Managers for Composite Keys

class CompositeKeyManager(models.Manager):
    def get_by_composite_key(self, **kwargs):
        """Get object by composite key fields"""
        return self.get(**kwargs)
    
    def filter_by_partial_key(self, **kwargs):
        """Filter by partial composite key"""
        return self.filter(**kwargs)

class OrderLine(models.Model):
    order_number = models.CharField(max_length=20)
    line_number = models.IntegerField()
    product = models.ForeignKey('Product', on_delete=models.CASCADE)
    quantity = models.IntegerField()
    unit_price = models.DecimalField(max_digits=10, decimal_places=2)
    
    objects = CompositeKeyManager()
    
    class Meta:
        unique_together = [['order_number', 'line_number']]
        ordering = ['order_number', 'line_number']
    
    def __str__(self):
        return f"Order {self.order_number}, Line {self.line_number}"

# Usage
line = OrderLine.objects.get_by_composite_key(
    order_number='ORD-2023-001',
    line_number=1
)

lines = OrderLine.objects.filter_by_partial_key(order_number='ORD-2023-001')

Migrations with Composite Keys

# migrations/0001_initial.py
from django.db import migrations, models

class Migration(migrations.Migration):
    dependencies = []
    
    operations = [
        migrations.CreateModel(
            name='ProductPrice',
            fields=[
                ('id', models.BigAutoField(primary_key=True)),
                ('product_code', models.CharField(max_length=20)),
                ('effective_date', models.DateField()),
                ('price', models.DecimalField(max_digits=10, decimal_places=2)),
            ],
        ),
        migrations.AddConstraint(
            model_name='productprice',
            constraint=models.UniqueConstraint(
                fields=['product_code', 'effective_date'],
                name='unique_product_price'
            ),
        ),
        migrations.AddIndex(
            model_name='productprice',
            index=models.Index(
                fields=['product_code', 'effective_date'],
                name='product_price_idx'
            ),
        ),
    ]

Performance Considerations

# Optimize queries with composite keys
class OptimizedCompositeModel(models.Model):
    key1 = models.CharField(max_length=50)
    key2 = models.IntegerField()
    data = models.TextField()
    
    class Meta:
        unique_together = [['key1', 'key2']]
        indexes = [
            # Composite index for lookups
            models.Index(fields=['key1', 'key2']),
            # Individual indexes if needed
            models.Index(fields=['key1']),
        ]
    
    @classmethod
    def bulk_get_by_keys(cls, key_pairs):
        """Efficiently fetch multiple records by composite keys"""
        from django.db.models import Q
        
        query = Q()
        for key1, key2 in key_pairs:
            query |= Q(key1=key1, key2=key2)
        
        return cls.objects.filter(query)

# Usage
key_pairs = [('A', 1), ('B', 2), ('C', 3)]
results = OptimizedCompositeModel.bulk_get_by_keys(key_pairs)

Testing Composite Key Models

from django.test import TestCase
from django.db import IntegrityError

class CompositeKeyModelTests(TestCase):
    def test_unique_together_constraint(self):
        """Test that composite key uniqueness is enforced"""
        OrderLine.objects.create(
            order_number='ORD-001',
            line_number=1,
            product=self.product,
            quantity=5,
            unit_price=10.00
        )
        
        # Attempting to create duplicate should fail
        with self.assertRaises(IntegrityError):
            OrderLine.objects.create(
                order_number='ORD-001',
                line_number=1,
                product=self.product,
                quantity=3,
                unit_price=10.00
            )
    
    def test_composite_key_lookup(self):
        """Test querying by composite key"""
        line = OrderLine.objects.create(
            order_number='ORD-001',
            line_number=1,
            product=self.product,
            quantity=5,
            unit_price=10.00
        )
        
        retrieved = OrderLine.objects.get(
            order_number='ORD-001',
            line_number=1
        )
        
        self.assertEqual(line.id, retrieved.id)

Best Practices

Use Django's Auto PK: Prefer Django's automatic primary key unless integrating with legacy systems
Natural Keys: Use natural keys for business logic, keep auto PK for Django internals
Unique Together: Use unique_together or UniqueConstraint for composite uniqueness
Index Properly: Create composite indexes for frequently queried key combinations
Document Clearly: Document composite key fields and their business meaning
Test Constraints: Thoroughly test uniqueness constraints and edge cases

While Django's traditional single-column primary keys work well for most cases, understanding composite key patterns is essential when working with legacy databases or specific data modeling requirements. The combination of Django's auto PK with unique constraints provides the best of both worlds.

Tablespaces

Tablespaces provide a way to define locations in the file system where database objects can be stored. While primarily a PostgreSQL and Oracle feature, understanding tablespaces helps optimize database performance by strategically placing data on different storage devices.

Database Instrumentation

Database instrumentation provides visibility into your application's database performance, helping you identify bottlenecks, monitor query patterns, and optimize database operations. Understanding how to implement comprehensive monitoring enables proactive performance management.