I'd like to scrape details of tennis matches from various websites and then combine them into a single database. Matches partially overlap across the websites and they contain different amounts of info.
As a simplified example of tables populated from scraping two websites:
website_A
table:
id_ | date | p1_name | p2_name | p1_serve_1_pct | p2_serve_1_pct |
---|---|---|---|---|---|
1 | 01-Jan | Roger Federer | Novak Djokovic | 70 | 65 |
2 | 02-Jan | Rafael Nadal | Andy Murray | 58 | 57 |
website_B
table:
id_ | date | p1_name | p2_name | p1_serve_1_pct | p2_serve_1_pct | p1_serve_2_pct | p2_serve_2_pct |
---|---|---|---|---|---|---|---|
1 | 01-Jan | Roger Federer | Novak Djokovic | 70 | 65 | 65 | 60 |
2 | 02-Jan | Rafael Nadal | Andy Murray | 68 | 67 | 59 | 58 |
3 | 03-Jan | Holgar Rune | Carlos Alcaraz | 70 | 67 | 57 | 58 |
In the above example the website_B
table has an extra match and two additional fields.
I need to matches these matches together, dedupe them and then combine them into a structure that I can query. Most of the ETL articles I've read seem to seem to suggest merging the data and then creating a combined table:
id_ | date | p1_name | p2_name | p1_serve_1_pct | p2_serve_1_pct | p1_serve_2_pct | p2_serve_2_pct |
---|---|---|---|---|---|---|---|
1 | 01-Jan | Roger Federer | Novak Djokovic | 70 | 65 | 65 | 60 |
2 | 02-Jan | Rafael Nadal | Andy Murray | 68 | 67 | 59 | 58 |
3 | 03-Jan | Holgar Rune | Carlos Alcaraz | 70 | 67 | 57 | 58 |
Note: In the above table the data in p1_serve_1_pct
and p2_serve_1_pct
are taken from website_B
during the merge process.
Querying the combined table is simple however it bugs me that I'm duplicating lots of the data. Additionally I won't know which fields were populated from which data source if I ever wanted to track back.
I think there's an alternative option with a master table that references the other two tables:
id_ | website_A_id | website_B_id |
---|---|---|
1 | 1 | 1 |
2 | 2 | 2 |
3 | 3 |
I would then write some logic into my queries to try and extract p1_serve_1_pct
and p2_serve_1_pct
from website_B
first and only if they were blank then to extract from website_A
.
This second option means I'm not duplicating data but my queries will be more complicated.
To my question(s) then... how do DBAs usually structure databases for this sort of scenario? Would my second approach work?
It's probably worth mentioning that in real life there are tables for players and tournaments too. I anticipate the row and column counts to be:
- Matches: 1.5m, 200
- Players: 100k, 30
- Tournaments: 30k: 30
Also probably worth mentioning that I've specifically steered clear of going into detail about how the merging process is done. I can add details if needed but it's basically a lot of fuzzy matching as there aren't common fields across data sources.