Modeling Time Spans in Django for an Archery Training Tracker

Based on your detailed description, you're facing a classic problem in relational database design: modeling hierarchical time spans with overlapping date ranges while maintaining data integrity. Let me suggest an approach that leverages Django's many-to-many relationships with an intermediary model.

Using Many-to-Many Relationships with Extra Fields

Instead of using direct foreign keys or relying solely on date-based queries, you could use Django's ability to create many-to-many relationships with extra fields on the relationship itself. This approach allows you to:

1. Maintain the hierarchical relationship between spans
2. Store additional metadata about the relationship
3. Avoid data redundancy
4. Perform efficient database-level queries

Here's how you might structure your models:

PYTHONCopied
from django.db import models
from django.core.exceptions import ValidationError

class Span(models.Model):
    code = models.CharField(max_length=30)
    start = models.DateField()
    end = models.DateField()
    user = models.ForeignKey('auth.User', on_delete=models.CASCADE)
    
    def clean(self):
        if self.start >= self.end:
            raise ValidationError("End date must be after start date")
    
    def save(self, *args, **kwargs):
        self.clean()
        super().save(*args, **kwargs)
    
    class Meta:
        abstract = True

class Season(Span):
    description = models.TextField(blank=True, null=True)

class Block(Span):
    title = models.CharField(max_length=30)
    seasons = models.ManyToManyField(Season, through='BlockSeasonRelation')

class Cycle(Span):
    title = models.CharField(max_length=30)
    blocks = models.ManyToManyField(Block, through='CycleBlockRelation')

class BlockSeasonRelation(models.Model):
    block = models.ForeignKey(Block, on_delete=models.CASCADE)
    season = models.ForeignKey(Season, on_delete=models.CASCADE)
    # You can add additional metadata about this relationship here
    
    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=['block', 'season'], 
                name='unique_block_season'
            )
        ]

class CycleBlockRelation(models.Model):
    cycle = models.ForeignKey(Cycle, on_delete=models.CASCADE)
    block = models.ForeignKey(Block, on_delete=models.CASCADE)
    # You can add additional metadata about this relationship here
    
    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=['cycle', 'block'], 
                name='unique_cycle_block'
            )
        ]

class Session(models.Model):
    date = models.DateField()
    duration = models.DurationField()
    arrows = models.IntegerField()
    user = models.ForeignKey('auth.User', on_delete=models.CASCADE)
    cycle = models.ForeignKey(Cycle, on_delete=models.PROTECT)
    session_number = models.PositiveIntegerField()
    
    # These methods help you access the hierarchy without redundant FKs
    def block(self):
        # Get the block that contains this session's date
        return Block.objects.filter(
            cycleblockrelation__cycle=self.cycle,
            start__lte=self.date,
            end__gt=self.date
        ).first()
    
    def season(self):
        block = self.block()
        if block:
            return Season.objects.filter(
                blockseasonrelation__block=block,
                start__lte=self.date,
                end__gt=self.date
            ).first()
        return None

Managing the Relationships

With this structure, you would create and manage the relationships like this:

PYTHONCopied
# Create spans
season = Season.objects.create(
    code='22-23', 
    start=date(2022, 8, 26), 
    end=date(2023, 8, 24),
    user=user
)

block1 = Block.objects.create(
    code='B01', 
    title='First Block',
    start=date(2022, 10, 1), 
    end=date(2022, 12, 29),
    user=user
)

# Create the relationship
BlockSeasonRelation.objects.create(block=block1, season=season)

# Similarly for cycles
cycle1 = Cycle.objects.create(
    code='C04',
    title='First Cycle',
    start=date(2022, 9, 21),
    end=date(2022, 10, 2),
    user=user
)

CycleBlockRelation.objects.create(cycle=cycle1, block=block1)

# Create a session
session = Session.objects.create(
    date=date(2022, 10, 1),
    duration=timedelta(minutes=90),
    arrows=100,
    user=user,
    cycle=cycle1,
    session_number=1
)

Validation and Consistency

To maintain data consistency, you could add validation methods:

PYTHONCopied
class BlockSeasonRelation(models.Model):
    # ... existing code ...
    
    def clean(self):
        # Ensure block dates are within season dates
        if (self.block.start < self.season.start or 
            self.block.end > self.season.end):
            raise ValidationError(
                "Block dates must be within season dates"
            )
    
    def save(self, *args, **kwargs):
        self.clean()
        super().save(*args, **kwargs)

class CycleBlockRelation(models.Model):
    # ... existing code ...
    
    def clean(self):
        # Ensure cycle dates are within block dates
        if (self.cycle.start < self.block.start or 
            self.cycle.end > self.block.end):
            raise ValidationError(
                "Cycle dates must be within block dates"
            )
    
    def save(self, *args, **kwargs):
        self.clean()
        super().save(*args, **kwargs)

Querying

This structure allows for powerful queries:

PYTHONCopied
# Get all cycles in a specific season
cycles_in_season = Cycle.objects.filter(
    blocks__seasons=season
)

# Get all sessions in a specific block
sessions_in_block = Session.objects.filter(
    cycle__blocks=block1
)

# Get all blocks that start in October
october_blocks = Block.objects.filter(
    start__month=10
)

Benefits of This Approach

1. Explicit Relationships: The relationships between spans are explicitly modeled
2. Data Integrity: Validation ensures date consistency
3. Efficient Queries: You can perform complex queries at the database level
4. Flexibility: You can add metadata to the relationships if needed
5. No Redundancy: Each piece of data is stored only once

This approach combines the best of both your options while avoiding their drawbacks. It maintains explicit relationships (like Option 1) without redundant foreign keys, and it allows for date-based validation without losing the ability to perform efficient database queries (unlike Option 2).