# Column classification (5 tables, no verified joins) The catalog profile lists 58 of the 78 columns (the card says 21 + 21 + 21 + 8 + 7). Each of the three match tables shows 17 columns, not 21, and `players_tournament_man_` shows 4 of 8. The classification below covers only the columns I could see. ## Identifiers There is no single-column surrogate ID anywhere. Identity comes from composite keys. - **`players_man_`:** `name` (one row per player). - **`players_tournament_man_`:** `name` + `year` + `tournament` (the catalog marks these as the primary key). - **`raw_kaggle`, `return_kaggle`, `serve_kaggle`:** one row per player per match, keyed by `Name` + `Date` + `Tournament` + `Rd`. The serve table spells the tournament column `Tournment`. - `Name` and `Tournament` also act as the foreign-key-like links. The catalog flags them as foreign keys on `return_kaggle`, but no cross-table join has been verified. - `against` (the opponent) is a descriptive name column, not a key. ## Categorical dimensions - **Surface:** `Surface`, with about 4 distinct values. - **Round:** `Rd`, with about 14 values; `rounds` in `players_tournament_man_`. - **Event:** `Tournament` / `Tournment` (about 3,963 distinct values, so very high cardinality) and `tournament` in `players_tournament_man_` (about 362). - **Players and opponents:** `Name` and `against`. - **Match outcome text:** `Score`. - **Break-point labels:** `BPCnv` (return) and `Bpsvd` (serve). These are stored as text like "x/y", not as numbers. ## Numeric metrics **Stored as true numbers (DOUBLE or BIGINT):** - **`raw_kaggle`:** - Serve and point counts: `Aces`, `DFs`, `1SP`, `2SP`, `SP`, `TP`. - Rankings and opponent stats: `Rk`, `vRk`, `vA`. - **`return_kaggle` and `serve_kaggle`:** `Rk` and `vRk` (ranking of the player and the opponent). - **`players_man_`:** `number_of_matches`. **Numeric in meaning but stored as text (VARCHAR):** - **Serve:** `1st%`, `1stIn`, `2nd%`, `A%`, `Df%`, `Dr`. - **Return:** `TPW`, `RPW`, `DR`, `v1st%`, `v2nd%`, `vA%`. The catalog mislabels most of these as "classifier" because of the text storage. They are really measures, probably percentages or ratios. They need stripping and casting before they can be averaged or modeled. ## Dates and times - **`Date`:** in all three match tables, stored as VARCHAR text rather than a DATE. Formats are mixed, including "1-Apr-2013" and "9‑Sep‑2024" with a non-standard hyphen, so parsing needs care. - **`Time`:** in all three match tables, also VARCHAR. - **`year`:** BIGINT, in `players_tournament_man_`. - **`playing_years`:** VARCHAR, in `players_man_`. It holds a list of years as text. ## Caveat The catalog's semantic roles are unreliable for the text-typed numeric columns and for the dates. Any analysis will need explicit casting and date parsing, and the roughly 20 columns missing from the profile should be checked with `inspect_columns` if they matter.