Where to Discuss?

Local Group


Goal: Playing SQL with Soccer Case. Overview and ERD.

I want to make refresh my SQL knowledge, and I’m looking for a case, my first choice is user management system, But this is too common. I also have my alumni case that I have made in 2007. And real life application turned out to be somehow weird, because it has a lot of hacky patch, to suit specific situation.

Instead of making cool complex project. I decide to start with humble data.

I need something down to earth. I want to bring social life, into an SQL tutorial. After a few options, I decide to make a fake soccer club.

The easier to read, the more useful for other people.

Building Soccer Case

Neighbourhood Gathering Club

We all love peace, but we all need healthy competition. Managing sport community have been, in our daily neighbourhood live. I don’t know anything about sport, that’s including soccer.

This does not stop me for build an imaginative players, along with all the team support. My first task, is to pick diverse names, from all over the world to as our soccer allstars,

name age roles job desc
Abdullah Al-Bakr 16 Support Cooking Chef
Ahmad Rahman 17 Players Defender
Akira Kato 16 Players Back
Alessia Bianchi 16 Support Equipment
Andi Suharto 15 Players Midfielder
Ekaterina Petrova 26 Support Bus Driver
Fatima Al-Khalifa 18 Support Cooking Chef
Gustav Andersen 15 Players Defender
Huda Al-Farsi 15 Players Goalkeeper
Jian Chen 15 Players Striker
Joko Susilo 15 Players Goalkeeper
Kim Kwan 35 Support Physiotherapist
Lia Wijaya 16 Coach Coach Trainee
Marco Rossi 17 Players Midfielder
Maria Svensson 18 Support Cooking Chef
Nikolai Ivanov 15 Players Winger
Nur Hidayah 16 Players Striker
Ratih Wijayanti 17 Support Physiotherapist
Ravi Singh 15 Players Striker
Sakura Yamamoto 18 Coach Coach Assistant
Smith Sonian 40 Coach Head Coach
Takumi Sato 17 Players Captain
Tetsuya Suzuki 18 Players Back
Vladimir Ivanov 15 Players Winger
Wilson Weasley 37 Support Bus Driver
Yan Liu 17 Support Equipment
Yusuf Abdullah 17 Players Winger
dr. Johnson Bun 50 Support Doctor
dr. Komal Sharma 26 Support Doctor


Imaginary Team

Neighbourhood is not all about sports, we also need community involvement to join voluntarily, That’s why we have medical services. We have coach, coach assistant, and also coach intern for anyone who wish to learn.

All the players are boys, from back/defender, winger, midfielder, goalpkeeper. Somebody have to lead as captain. And since it is voluntary club, some player might have other activities as well beside soccer, so we also require substitute players.

We can have other community for girls. But for now, all players are male teenagers. For other staff we respect gender diversity, and we also need involvement for various age, from this neighbourhood.

Since this club is more like gathering event, we also need a lot of food for fans. That’s why we have some three chefs, because we all love foods.

For 30 people, and food, and equipment, including sound system, we require good transportation. So here we are, having two bus driver.

Entity Relationship

Data Modelling

Instead of go directly coding, we need to anaylze what data model suitable, for our beloved neighbourhood.

As we have seen in above tables, we have three roles.

  • Players
  • Coach
  • Support

Now we can build a simple ERD diagram.

ERD: Simple Soccer

After a few iteration (an hour), I decide to split the responsilibity between players and staffs. These two tables are originally one table. But I need an additional column, such as, the players might have tag on, who’s in the bench and who is currently playing. And who act as captain. The saff also might other entity as well, such as who served as dedicated doctor, and the one who just come up voluntarily in weekend. Bottomline, staffs and players have, entirely distinct perspective.

ERD: Complete Soccer

It is easier to complete the model with attribute, after we get the final scheme. The PlantUML would be as below code:


!define MAX_TABLE_WIDTH 200

!define ROLE_BG_COLOR LightBlue
!define SEAT_BG_COLOR LightPink

hide empty members

title Team Allstars ERD

skinparam Table {
    BackgroundColor White
    BorderColor Black
    BackgroundColorHeader Grey
    BackgroundColorTitle Grey
    HeaderFontSize HEADER_FONT_SIZE
    DefaultFontSize DEFAULT_FONT_SIZE

entity People {
    * id : int <<generated>>
    name : varchar(255)
    email : varchar(100)
    age : int
    gender : varchar(50)

entity Roles {
    * id : int <<generated>>
    name : varchar(50)

entity Responsibilities {
    * id : int <<generated>>
    name : varchar(50)

entity Seats {
    * id : int <<generated>>
    name : varchar(50)

entity PeopleRoles {
    * person_id : int
    * role_id : int
    * People.id : PeopleRoles.person_id
    * Roles.id : PeopleRoles.role_id

entity PlayersSeats {
    * person_id : int
    * role_id : int
    * seat_id : int
    is_bench : boolean = false
    * People.id : PlayersSeats.person_id
    * Roles.id : PlayersSeats.role_id
    * Seats.id : PlayersSeats.seat_id

entity StaffResps {
    * person_id : int
    * role_id : int
    * resp_id : int
    * People.id : StaffResps.person_id
    * Roles.id : StaffResps.role_id
    * Responsibilities.id : StaffResps.resp_id

PeopleRoles }|..|| People
PeopleRoles }|..|| Roles
PlayersSeats }|..|| PeopleRoles
StaffResps }|..|| PeopleRoles
PlayersSeats }--|| Seats : occupies
PlayersSeats }--|| Roles : has
StaffResps }--|| Responsibilities : has
StaffResps }--|| Roles : is

skinparam Roles {
    BackgroundColor LightBlue

skinparam Responsibilities {
    BackgroundColor LightGreen

skinparam Seats {
    BackgroundColor LightPink


ERD: Allstars Soccer

I use draw.io to generate the URD using PlantUML format. You can obtain the PlantUML source code here:

I thinks that’s all. We can continue our imaginary club, with real SQL statement.

What is Next 🤔?

Enough with modelling. We need an actual data.

Consider continue reading [ Soccer - Fixtures ].

Thank you for reading.