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

Question

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 questions then...

1. Should I follow the first approach and just accept the data duplication? If yes, how would I create an audit trail back to the original data sources?
2. Would my second approach work? Are there are considerations to be aware of before going down this route?

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.

Answer 1

I would loosely borrow concepts from data warehousing.

Firstly build staging tables that are largely 1-for-1 with each source. Populate these from your web scraping.

For the "proper" database, build dimension table players from all sources.

Also matches from all websites (date + 2 players).

Then your score % etc. become facts held against that match with the source.

If you have discrepancies in the facts from the various sources, then you need to decide what to do with them as you populate from staging. If the facts concur, the source may become irrelevant, though of course you still have it in staging.

From there, you have a nice, easy data model.

PlayerDimension (id, name)

MatchDimension (id, player1id, player2id, date)

FirstServePercentFact( matchID, playerID, firstPercent)

SecondServePercentFact( matchID, playerID, secondPercent)

ShirtColourFact( matchID,playerID, colour)

If you wanted scores, you could extend the concept, either via set, or match:

SetScoreFact(matchID,playerID, set, score)

Answer 2

I know you have asked about mysql, but depending on a variety of things, you might actually want to use a NoSQL database for this. I know you say duplicating data bugs you, and I get it, but denormalization is SOP in the NoSQL world. I'm tempted to suggest mongo, even though these aren't really documents. Doesn't seem like the right job for Redis. Cassandra kinda seems like a good fit, but might be overkill. idk, I guess that is the answer to a different question, but figured I would provide a bit more detail in case you don't know anything about NoSQL dbs. Probably others can chime in on what they think would be a good fit.