Eurovision-25-backend/docs/database_schema.md

Database Schema Documentation

Overview

This document provides a high-level overview of the database schema used by the Eurovision 25 Homereview Backend. The application uses SQLite as its database and SQLModel for ORM and data validation.

Note: This documentation describes the intended structure. The definitive source of truth for column types, constraints, and precise relationships is the code within the src/models/ directory.

---

Tables

1. Group

Stores information about the different households or groups participating.

Column Name	Type	Description
id	Integer	Primary Key. Unique identifier for the group.
name	String	The name of the household/group (e.g., "Smith Household", "Team Awesome"). Should be unique.
created_at	DateTime	Timestamp when the group was created.
updated_at	DateTime	Timestamp when the group was last updated.

2. User

Stores information about individual users (players and admins).

Column Name	Type	Description
id	Integer	Primary Key. Unique identifier for the user.
username	String	The user's login name. Should be unique.
hashed_password	String	The securely hashed password for the user.
email	String	User's email address. Should be unique. (Nullable if not immediately required)
group_id	Integer	Foreign Key -> Group.id. The group this user belongs to.
avatar_id	Integer	Identifier for the user's chosen avatar. Corresponds to an image file named {avatar_id}.jpg (e.g., 1.jpg). Defaults to 0, representing a generic default avatar (0.jpg).
is_active	Boolean	Flag indicating if the user account is active (Default: true).
is_admin	Boolean	Flag indicating if the user has administrator privileges (Default: false).
created_at	DateTime	Timestamp when the user was created.
updated_at	DateTime	Timestamp when the user was last updated.

3. Song

Stores information about each participating song in the contest.

Column Name	Type	Description
id	Integer	Primary Key. Unique identifier for the song.
year	Integer	The year of the Eurovision contest (e.g., 2025). Indexed.
country	String	The participating country name.
artist	String	The name of the performing artist(s).
title	String	The title of the song.
running_order	Integer	The official order in which the song appears in the show.
lyrics_original	String	The original lyrics of the song. (Nullable)
lyrics_translation_fi	String	Finnish translation of the lyrics. (Nullable)
tags	String	Comma-separated list of central themes or tags (e.g., "joy,partying,love"). (Nullable)
created_at	DateTime	Timestamp when the song entry was created.
updated_at	DateTime	Timestamp when the song entry was last updated.

4. Review

Stores the scores and comments submitted by a user for a specific song. This table links Users and Songs.

Column Name	Type	Description
id	Integer	Primary Key. Unique identifier for the review entry.
user_id	Integer	Foreign Key -> User.id. The user who submitted the review.
song_id	Integer	Foreign Key -> Song.id. The song being reviewed.
score_song	Integer	The numerical score (1-100) for the song quality. Required.
score_show	Integer	The numerical score (1-100) for the stage show. Required.
score_wardrobe	Integer	The numerical score (1-100) for the wardrobe/costumes. Required.
text_review	String	Optional textual comments from the user. (Nullable)
created_at	DateTime	Timestamp when the review was initially created.
updated_at	DateTime	Timestamp when the review was last modified.

Constraints:

• A Unique Constraint should exist on the combination of (user_id, song_id) to ensure each user can only submit one review per song.

---

Relationships Summary

• Group to User: One-to-Many (One Group can have many Users).
• User to Review: One-to-Many (One User can submit many Reviews).
• Song to Review: One-to-Many (One Song can receive many Reviews).
• User/Song to Review: A Review represents a specific rating given by one User for one Song.

---

Database Views for Results Calculation

Database Views are stored queries that can be accessed like regular tables. They can be useful for encapsulating complex data retrieval logic, such as calculating aggregated results.

Potential Views:

• view_song_results_group: Calculates average scores per song, aggregated only for users within the same group.
• view_song_results_all: Calculates average scores per song, aggregated across all active users.
• view_overall_stats_group: Calculates statistics (best song, best wardrobe avg, etc.) based on group results.
• view_overall_stats_all: Calculates statistics based on all user results.

Implementation Considerations:

1. Calculation Logic: The views would contain SQL queries joining Review, User, and potentially Song tables, using aggregate functions (AVG, COUNT, MAX, etc.) and grouping by song_id and potentially group_id.
2. SQLite Support: SQLite fully supports Views.
3. SQLModel/FastAPI Interaction:
   • You can query Views using raw SQL execution via SQLAlchemy (which SQLModel uses under the hood).
   • Mapping SQLModel classes directly to Views for full ORM features can sometimes be tricky, especially if the View doesn't have a clear primary key equivalent or if you need write operations (which Views generally don't support directly).
   • For read-only results endpoints, executing raw SQL against a View and then potentially parsing the results into Pydantic models (schemas) for the API response is a viable approach.
4. Alternative - Application Layer Calculation: The alternative is to perform these calculations within your FastAPI application code (e.g., in your CRUD functions or service layer). This involves fetching the raw review data using SQLModel/SQLAlchemy and then processing it in Python.
   • Pros: Keeps all logic in Python, potentially easier to test with Python tools, more ORM-friendly data fetching.
   • Cons: Can be less performant for very large datasets compared to letting the database do the aggregation (though likely not an issue for your scale), might duplicate calculation logic if needed in multiple places.

Decision for this Project:

Given the expected scale (small group) and the simplicity of the calculations (averages, max), performing the aggregations within the FastAPI application layer when the /results endpoints are called is likely the simplest and most maintainable approach. It avoids managing separate View definitions and potential complexities with ORM mapping to Views.

However, creating Views directly in SQLite could be considered if performance becomes a concern or if you prefer encapsulating the aggregation logic purely in SQL.

---

(Optional: You could embed a simple diagram here if you create one, e.g., using Mermaid syntax or linking to an image file)