Motivatii Alegere Concret Table

concret-table.txt

. Inheritance Strategy: Concrete Table Inheritance
The scenario requires managing sport-specific player statistics that vary significantly in structure between sports. For example, a football player accumulates yellow and red cards and shots on target, while a swimmer is measured by stroke type and best time in seconds, and an athletics competitor has a personal best distance or time per event type.

https://martinfowler.com/eaaCatalog/inheritanceMappers.html

Three primary strategies exist for mapping object inheritance to relational tables: Single Table Inheritance, Class Table (Joined) Inheritance, and Concrete Table Inheritance.

1 Strategies Considered
Single Table Inheritance
All subtypes are merged into one table with nullable columns for subtype-specific attributes. This was rejected because it would require storing NULL values for every column irrelevant to a given sport. With three sports and six to eight unique columns each, the resulting table would contain a large number of NULL entries per row, wasting storage and introducing ambiguity in NOT NULL constraints (Celko, 2005).

Joined Table (Class Table) Inheritance
A shared table stores common attributes and each subtype has a separate table storing only its unique attributes, joined via primary key. This was considered but rejected due to the requirement for frequent per-sport statistical queries. Each query would require an additional JOIN, and since the subtypes share no columns beyond player_id, the JOIN provides no structural benefit >>>> only additional query complexity (Fowler, 2002).

Concrete Table Inheritance >>>> Selected Strategy
Each subtype has its own fully self-contained table with its sport-specific columns. The player_id column in each subtype table acts as both a primary key surrogate and a foreign key referencing the PLAYER supertable. A UNIQUE constraint on player_id in each subtable enforces the one-to-one relationship, ensuring that a player can have at most one row in each sport-specific statistics table.

2 Justification
Concrete Table Inheritance was selected for the following reasons specific to this design:

Sport statistics are always queried per sport >>>> a query for swimming results never needs football data, making UNION across subtypes unnecessary in practice.
Each subtype has a distinct and non-overlapping set of columns. No columns are shared between football_stats, swimming_stats, and athletics_stats beyond the player_id foreign key.
NULL columns are eliminated entirely. Every column in each subtype table is meaningful and potentially NOT NULL, supporting stronger data validation.
Query performance on individual sport statistics is optimal — no JOIN to a shared base table is required to retrieve subtype-specific data.

The trade-off is that cross-sport queries (e.g., all statistics for a player who competes in multiple sports) require a UNION operation. However, given the tournament context — where a player competes in a single sport — this scenario is unlikely. Concrete Table Inheritance is most appropriate  This accurately describes the current schema.