Soccer - Overview

Preface

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,

Responsibility

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.

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.

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

@startuml

!define TABLE_BORDER_THICKNESS 2
!define MAX_TABLE_WIDTH 200
!define HEADER_FONT_SIZE 14
!define DEFAULT_FONT_SIZE 12

!define ROLE_BG_COLOR LightBlue
!define RESPONSIBILITY_BG_COLOR LightGreen
!define SEAT_BG_COLOR LightPink

hide empty members

title Team Allstars ERD

skinparam Table {
    BackgroundColor White
    BorderThickness TABLE_BORDER_THICKNESS
    BorderColor Black
    BackgroundColorHeader Grey
    BackgroundColorTitle Grey
    MaxWidth MAX_TABLE_WIDTH
    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
}

@enduml

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

• github.com/…/soccer/schema.puml

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.