Skip to main content
2 of 3
added 4 characters in body
Jossy
  • 83
  • 9

How to design a database that needs to merge similar data from different sources?

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.

Jossy
  • 83
  • 9