Data Models - FKApi

Overview

FKApi uses Django ORM models to represent football kit data. The schema is designed to normalize data while maintaining query performance through strategic use of foreign keys, many-to-many relationships, and database indexes. All models are defined in fkapi/core/models.py.

Entity Relationship Diagram

Core Models

Kit

The central model representing a football kit (jersey/uniform).

CharField(100)

required

Kit name (indexed for search performance)

CharField(150)

required

URL-friendly identifier (unique). Allows special characters including Romanian characters (ăâîșț). Must match pattern: [-a-zA-Z0-9_ăâîșțĂÂÎȘȚ]+

CharField(20)

Original ID from FootballKitArchive.com for URL construction

ForeignKey(Club)

required

Club that owns this kit

ForeignKey(Season)

required

Season this kit was used

ManyToManyField(Competition)

Competitions this kit was used in

ForeignKey(Type_K)

required

Type of kit (Home, Away, Third, etc.)

ForeignKey(Brand)

required

Manufacturer/brand of the kit

URLField

required

URL to main kit image

DecimalField(3,2)

required

User rating (0.00-10.00)

URLField

Link to FootyHeadlines.com

DateTimeField

Last update time from source website

DateTimeField

Last update time in database (auto-updated)

ForeignKey(Color)

Primary color of the kit

ManyToManyField(Color)

Secondary colors of the kit

CharField(100)

Design description or pattern name

Indexes

Kit model has the following database indexes for query optimization:

name (single field)
team, season (composite)
main_img_url (single field)
web_updated (single field)
last_updated (single field)
rating (single field)

from core.models import Kit

# Get all home kits for a specific club in 2024 season
kits = Kit.objects.filter(
    team__slug='manchester-united',
    season__first_year='2024',
    type__name='Home'
).select_related('team', 'season', 'brand', 'type')

# Get kit with all relationships loaded
kit = Kit.objects.select_related(
    'team', 'season', 'brand', 'type', 'primary_color'
).prefetch_related(
    'competition', 'secondary_color'
).get(slug='kit-slug-here')

Club

Represents a football club/team.

IntegerField

Original ID from footballkitarchive.com

CharField(500)

required

Club name (indexed for search performance)

CharField(150)

required

URL-friendly identifier (unique). Supports special characters like Romanian letters.

URLField

URL to club logo

URLField

URL to dark mode logo

CountryField

Country where club is based

Indexes

name
country

from core.models import Club

# Get club by slug
club = Club.objects.get(slug='barcelona')

# Get all clubs from Spain
spanish_clubs = Club.objects.filter(country='ES')

Season

Represents a football season. Supports both single-year (“2024”) and two-year (“2023-24”) formats.

CharField(9)

required

Full season identifier (unique). Examples: “2024”, “2023-24”, “1999-00”

CharField(4)

required

First year of the season (4-digit year)

CharField(4)

Second year of the season for two-year formats (4-digit year)

from core.models import Season

# Single-year season
season_2024 = Season.objects.create(
    year='2024',
    first_year='2024',
    second_year=None
)

# Two-year season
season_23_24 = Season.objects.create(
    year='2023-24',
    first_year='2023',
    second_year='2024'
)

The scraper automatically handles various season format inputs including “23-24”, “2023-24”, “2023-2024” and normalizes them to the standard format.

Type_K

Represents the type of kit with categorization and ordering.

Model name uses Type_K (not Type) to avoid conflicts with Python’s type keyword and match legacy naming.

CharField(100)

required

Type name (e.g., “Home”, “Away”, “Third”, “GK Home”)

CharField(20)

required

Category for organization. Choices:

match: Game kits (default)
prematch: Pre-match, bench, warm-up, staff
preseason: Pre-season, temporary
training: Training
travel: Travel, polo
jacket: Jackets (anthem, rain, windbreaker, track, vest)

IntegerField

required

Order of category (1-6):

1 = match
2 = prematch
3 = preseason
4 = training
5 = travel
6 = jacket

IntegerField

default:"999"

Priority within category (lower = first). Non-GK items come before GK items.

BooleanField

default:"False"

True if this is a goalkeeper kit (GK, Goalkeeper, Portero)

Ordering

Default ordering: ['category_order', 'is_goalkeeper', 'order_priority', 'name'] This ensures:

Match kits appear first
Outfield kits before goalkeeper kits within each category
Standard types (Home, Away, Third) before special types

Indexes

Composite: (category_order, is_goalkeeper, order_priority)
Composite: (category, is_goalkeeper, order_priority)

from core.models import Type_K

# Create and auto-categorize
kit_type = Type_K.objects.create(name='Home')
kit_type.categorize()  # Auto-sets category, category_order, etc.
kit_type.save()

# Query by category
match_kits = Type_K.objects.filter(category='match')

Brand

Represents a kit manufacturer/brand.

CharField(100)

required

Brand name (indexed)

SlugField(150)

required

URL-friendly identifier (unique)

URLField

URL to brand logo

URLField

URL to dark mode logo

Index

name

from core.models import Brand

# Common brands
nike = Brand.objects.get(slug='nike-kits')
adidas = Brand.objects.get(slug='adidas-kits')

# Get all kits by brand
nike_kits = nike.kit_set.all()

Competition

Represents a football competition.

CharField(100)

required

Competition name (indexed)

SlugField(150)

required

URL-friendly identifier (unique)

URLField

URL to competition logo

URLField

URL to dark mode logo

CountryField

Country where competition is based

Indexes

name
country

from core.models import Competition

# Get competition
pl = Competition.objects.get(slug='premier-league')

# Get all kits used in a competition
pl_kits = pl.kit_set.all()

Color

Represents a color used in kit designs.

CharField(100)

required

Name of the color (e.g., “Red”, “Blue”, “Navy”)

ColorField

default:"#FF0000"

Hex color code (e.g., “#FF0000” for red)

from core.models import Color

red = Color.objects.create(
    name='Red',
    color='#FF0000'
)

Variation

Represents a color variation (shade) of a parent color.

CharField(100)

required

Name of the variation (e.g., “Light Blue”, “Dark Red”)

ForeignKey(Color)

required

Parent color this variation belongs to

ColorField

default:"#FF0000"

Hex color code for the variation

IntegerField

default:"0"

Red component (0-255)

IntegerField

default:"0"

Green component (0-255)

IntegerField

default:"0"

Blue component (0-255)

from core.models import Color, Variation

blue = Color.objects.get(name='Blue')

light_blue = Variation.objects.create(
    name='Light Blue',
    parent_color=blue,
    color='#ADD8E6',
    color_r=173,
    color_g=216,
    color_b=230
)

Relationships

One-to-Many Relationships

Club → Kit

One club has many kits

club.kit_set.all()

Season → Kit

One season has many kits

season.kit_set.all()

Brand → Kit

One brand has many kits

brand.kit_set.all()

Type_K → Kit

One type has many kits

kit_type.kit_set.all()

Color → Variation

One color has many variations

color.variation_set.all()

Many-to-Many Relationships

Kit ↔ Competition

Kits can be used in multiple competitions

kit.competition.all()
competition.kit_set.all()

Kit ↔ Color (secondary)

Kits can have multiple secondary colors

kit.secondary_color.all()
color.secondary_color.all()

Query Optimization

select_related() vs prefetch_related()

Use select_related() for foreign key relationships (SQL JOIN):

# Efficient: Single query with JOIN
kits = Kit.objects.select_related(
    'team',
    'season', 
    'brand',
    'type',
    'primary_color'
).all()

Use prefetch_related() for many-to-many and reverse foreign key relationships:

# Efficient: Two queries total
kits = Kit.objects.prefetch_related(
    'competition',
    'secondary_color'
).all()

Combined Example

# Optimal query for kit detail page
kit = Kit.objects.select_related(
    'team',
    'season',
    'brand',
    'type',
    'primary_color'
).prefetch_related(
    'competition',
    'secondary_color'
).get(slug='kit-slug')

# Now all related data is loaded without additional queries
print(kit.team.name)  # No query
print(kit.brand.name)  # No query
for comp in kit.competition.all():  # No query
    print(comp.name)

Custom Fields

ColorField

Provided by django-colorfield package:

Stores hex color codes
Includes color picker widget in admin
Used in Color and Variation models

CountryField

Provided by django-countries package:

Stores ISO 3166-1 country codes
Provides country name display
Used in Club and Competition models

Custom Slug Field

Both Kit and Club use custom slug validators that allow special characters:

Pattern: [-a-zA-Z0-9_ăâîșțĂÂÎȘȚ]+
Supports Romanian characters
More permissive than Django’s default SlugField

Database Considerations

Indexes

Strategic indexes improve query performance:

Single-field indexes on frequently searched columns (name, slug)
Composite indexes for common filter combinations (team+season)
Category ordering indexes for Type_K sorting

Connection Pooling

PostgreSQL connection settings in settings.py:

CONN_MAX_AGE: 60 seconds
Keepalive settings for connection stability
Reduces connection overhead

Transactions

Scraping operations use atomic transactions:

from django.db import transaction

with transaction.atomic():
    club.save()
    kit.save()
    # All-or-nothing operation

Model Methods

Type_K.categorize()

Automatically categorizes and sets ordering fields based on the type name:

kit_type = Type_K(name='GK Home')
kit_type.categorize()
kit_type.save()

# Auto-sets:
# - category='match'
# - category_order=1
# - is_goalkeeper=True
# - order_priority=<appropriate value>

This method is called automatically when new kit types are created during scraping.

Architecture

Learn about system architecture

Scraping

Understand how data is collected

​Overview

​Entity Relationship Diagram

​Core Models

​Kit

​Indexes

​Club

​Indexes

​Season

​Type_K

​Ordering

​Indexes

​Brand

​Index

​Competition

​Indexes

​Color

​Variation

​Relationships

​One-to-Many Relationships

Club → Kit

Season → Kit

Brand → Kit

Type_K → Kit

Color → Variation

​Many-to-Many Relationships

Kit ↔ Competition

Kit ↔ Color (secondary)

​Query Optimization

​select_related() vs prefetch_related()

​Combined Example

​Custom Fields

​ColorField

​CountryField

​Custom Slug Field

​Database Considerations

​Model Methods

​Type_K.categorize()

​Related Documentation

Architecture

Scraping

Overview

Entity Relationship Diagram

Core Models

Kit

Indexes

Club

Indexes

Season

Type_K

Ordering

Indexes

Brand

Index

Competition

Indexes

Color

Variation

Relationships

One-to-Many Relationships

Many-to-Many Relationships

Query Optimization

select_related() vs prefetch_related()

Combined Example

Custom Fields

ColorField

CountryField

Custom Slug Field

Database Considerations

Model Methods

Type_K.categorize()

Related Documentation