Data Modeling: FIFA World Cup 2022

<p id="">It’s World Cup time and we’re so excited about it!</p><p id=""></p><p id="">So it’s a good time to see how we could model on a database the event, think about the entities and how they relate to each other.</p><p id=""></p><p id="">Before starting, I want to introduce two abbreviations we’re going to use on each table to make it more clear:</p><p id=""></p><ul id=""><li id="">PK - Primary Key: This is to identify the field that makes each row on the table unique. In this case, it’s always the id row.</li></ul><p id=""></p><ul id=""><li id="">FK - Foreign Key: This is to identify the field that relates a table to another. In practice, this is the id of another entity.</li></ul><p id=""></p><p id="">When starting to think about a model, a good approach is to first tackle the smallest pieces of data, tables that don't make much sense alone but will serve to other ones.</p><p id=""></p><h3 id=""><strong id="">Country</strong></h3><p id=""></p><p id="">First, we have to start with the participants of the event, the countries.</p><p id="">We will include the name as string and also a string for the flag, which represents the image url. </p><p id=""></p><figure id="" class="w-richtext-figure-type-image w-richtext-align-fullwidth" style="max-width:476px" data-rt-type="image" data-rt-align="fullwidth" data-rt-max-width="476px"><div id=""><img src="https://cdn.prod.website-files.com/65bd01fbefb223c77ff70cf3/65d6159a76cb791e6eaaa452_6388aba5066e0e852de4c79c_Country.webp" width="auto" height="auto" alt="" loading="auto" id=""></div></figure><p id=""></p><h3 id=""><strong id="">Stadium</strong></h3><p id=""></p><p id="">The stadium will only have its name since it is just a fun fact for reference that does not give too much value in this case.</p><p id=""></p><figure id="" class="w-richtext-figure-type-image w-richtext-align-fullwidth" style="max-width:476px" data-rt-type="image" data-rt-align="fullwidth" data-rt-max-width="476px"><div id=""><img src="https://cdn.prod.website-files.com/65bd01fbefb223c77ff70cf3/65d6159a76cb791e6eaaa446_6388abaf1f38e46ca5148d4a_Stadium.webp" width="auto" height="auto" alt="" loading="auto" id=""></div></figure><p id=""></p><h3 id=""><strong id="">Match type</strong></h3><p id=""></p><p id="">This entity is needed to identify whether a match is part of the group stage or knockouts.</p><p id="">That is why it does not have more than a name to represent it.</p><p id=""></p><figure id="" class="w-richtext-figure-type-image w-richtext-align-fullwidth" style="max-width:476px" data-rt-type="image" data-rt-align="fullwidth" data-rt-max-width="476px"><div id=""><img src="https://cdn.prod.website-files.com/65bd01fbefb223c77ff70cf3/65d6159a76cb791e6eaaa440_6388abcc8e747a4a0f0e017e_Match%2520type.webp" width="auto" height="auto" alt="" loading="auto" id=""></div></figure><p id=""></p><p id="">After defining the smaller entities, we can continue with the second level which includes the first ones.</p><p id=""></p><h3 id=""><strong id="">Player</strong></h3><p id=""></p><p id="">This entity not only contains information about the player like name, the shirt number and the birth date but it also links each player with their corresponding country.</p><p id=""></p><figure id="" class="w-richtext-figure-type-image w-richtext-align-fullwidth" style="max-width:476px" data-rt-type="image" data-rt-align="fullwidth" data-rt-max-width="476px"><div id=""><img src="https://cdn.prod.website-files.com/65bd01fbefb223c77ff70cf3/65d6159a76cb791e6eaaa449_6388abd576d96ec1e46c0c3a_Players.webp" width="auto" height="auto" alt="" loading="auto" id=""></div></figure><h3 id=""><strong id="">Match</strong></h3><p id=""></p><p id="">As expected, this is one of the most important entities, this is where all happens.</p><p id=""></p><p id="">The match has a date and it is linked with the two participant countries and the stadium.</p><p id=""></p><p id="">It does not contain a result or some other information because we will calculate all of this dynamically.</p><p id=""></p><figure id="" class="w-richtext-figure-type-image w-richtext-align-fullwidth" style="max-width:476px" data-rt-type="image" data-rt-align="fullwidth" data-rt-max-width="476px"><div id=""><img src="https://cdn.prod.website-files.com/65bd01fbefb223c77ff70cf3/65d6159a76cb791e6eaaa456_6388abbc4697995eab58107f_Match.webp" width="auto" height="auto" alt="" loading="auto" id=""></div></figure><p id=""></p><h3 id=""><strong id="">Goal</strong></h3><p id=""></p><p id="">Finally, the entity that does everything to make sense is the goal.</p><p id="">It contains the date with the minutes and seconds when it was done, the player (from where we can infer the country) and the match.</p><p id=""></p><figure id="" class="w-richtext-figure-type-image w-richtext-align-fullwidth" style="max-width:476px" data-rt-type="image" data-rt-align="fullwidth" data-rt-max-width="476px"><div id=""><img src="https://cdn.prod.website-files.com/65bd01fbefb223c77ff70cf3/65d6159a76cb791e6eaaa44d_6388abf923abc4f0c56d17eb_Goal.webp" width="auto" height="auto" alt="" loading="auto" id=""></div></figure><p id=""></p><p id="">Something to take in consideration is that in this example, we are not taking care about performance, since there is no context on which application this could be used. That’s why, all the values that can be calculated dynamically, were not represented on the tables.</p><p id=""></p><p id="">Finally, if we take a look at the entire database model, it would be something like this:</p><p id=""></p><figure id="" class="w-richtext-figure-type-image w-richtext-align-fullwidth" style="max-width:1450px" data-rt-type="image" data-rt-align="fullwidth" data-rt-max-width="1450px"><div id=""><img src="https://cdn.prod.website-files.com/65bd01fbefb223c77ff70cf3/65d6159a76cb791e6eaaa459_6388ac05a2089ea39358c8cd_all.webp" width="auto" height="auto" alt="" loading="auto" id=""></div></figure><p id=""></p><p id="">We can notice that countries and matches have a many to many relationship but in this case we don’t need a table for the relation because the match has a specific number of countries on it.</p><p id=""></p><p id="">Just to finish, I would like to comment that there is more than one right way to data model anything, so in this article I present, based on my experience and with no application context the best approach I consider, which could not be the best for other developers and that’s fine.</p><p id=""></p><p id="">Thanks for reading and keep enjoying the World Cup 2022!</p><p id=""></p><p id=""></p>