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.

  • Your second option is what's called an EAV (entity-attribute-value) model; you can search this site (and others) for the reasons why it's usually a bad idea. Overall, your question invites opinions, not objective answers.
    – mustaccio
    Commented Feb 2, 2023 at 20:18
  • Is the data on different servers? (That is a problem.) Or in different DATABASES? (Simply use db.tbl syntax.) Or what?
    – Rick James
    Commented Feb 2, 2023 at 21:01
  • @mustaccio - thanks for responding. I've updated the question to be more specific. I've read up on EAV models but they don't exactly seem to fit my case. The examples I've read cover situations where entities can have different combinations of attributes. However, in my example each entity (a tennis match) needs to have all attributes (statistics) - they just need to be extracted from different tables where some attributes are duplicated.
    – Jossy
    Commented Feb 2, 2023 at 21:08
  • @RickJames - the data is currently all in one database.
    – Jossy
    Commented Feb 2, 2023 at 21:09
  • Are the desired attributes available in all the tables? Please add SHOW CREATE TABLE for just 2 different tables. And mockup a CREATE for the combined table. Then we can give you specific code for that mockup.
    – Rick James
    Commented Feb 2, 2023 at 21:13

2 Answers 2


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)
  • Thanks for this :) I have a few clarifications though! i) What do you mean by a "dimension" table? ii) When you say "build dimension table players from all sources" - this would mean creating a single player record in the "proper" database for every unique player from across the data sources? iii)When you say "Then your score % etc. become facts held against that match with the source" - does this mean you would leave the score %s in the source data and link the match table in the "proper" database to the data sources?
    – Jossy
    Commented Aug 22, 2023 at 20:56
  • One additional Q... I note that you proposed new tables for each statistic (e.g. FirstServePercentFact) - was there a specific reason you didn't leave them within the match table as they are always recorded against a given match? I should mention that there are several dozen of these statistics so that's quite a few extra tables all with a very similar structure. In the past I have considered an EAV table similar to your approach - so a StatFact table with matchID, playerID,stattypeID and statvalue columns. However, most people here seem to advise against EAV tables...
    – Jossy
    Commented Aug 22, 2023 at 21:02
  • i, ii) Yes - build a single "golden record" curated from all sources for players & matches. iii) No - once you have cleansed & curated the facts, copy the data out of staging into the fact table. Additional Q: EAVs will trip you up. Build more dimensions or facts as you need them. Consider when you enhance what seems to be a simple bit of data , e.g. for a set, you might decide to add set duration. EAV you've got nowhere to go. It seems like a lot of typing but doing it like this is objectively a better model when you start to use and analyse your data. Commented Aug 23, 2023 at 1:36
  • Awesome thanks. A last follow up on the final question... one of the main queries I have to build needs to extract all stats by match by player in a flat file - one match per row. I have around 250 stats so that's 250 tables which means a query with 500 joins (1 per player per stat table). This is a necessary evil?
    – Jossy
    Commented Aug 23, 2023 at 13:58
  • 1
    Yes in theory it would but use common sense. The approach is sound, but a bit purist if taken literally. The key concepts are around staging, curation and building clean data. If it makes sense for you you to accumulate stats onto a match/player/whatever table then of course do so. It kind of comes down to how complete your data is. Do you have a handful of missing stats or up to 500? Do you get all the data at the same time or does it accumulate over multiple sources/pulls? You've got the foundation - you just need to adapt and apply to your situation. You've got this :) Commented Aug 23, 2023 at 14:49

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.