Introduction
This page aims to explain the SQL project, how it works, how to maintain it, and how it benefits the wiki. You can find it at https://witcheffect.com/4cc/sql/.
The wiki is an excellent resource for finding information, but compiling that information can require some effort. The issue here is that effort is usually not shared, and so everytime someone new wants to start collecting data for some stat or page, they usually have to start from scratch and comb the wiki. This is an incredible waste of time and ideally, data should only be entered once. Not only does that save time, but it also reduces the chances of inaccurate/conflicting information caused by multiple people doing their own thing. This is where the SQL project comes in, so let's explain how it works first.
SQL Structure
For those with little or no exposure, SQL is a database system composed of tables. These tables have defined columns used for whatever is required to represent something in the cup. The key thing here is that tables are not connected to each other automatically and it is up to us to be able to link them together. This is done by having unique ID numbers associated with each record. If you don't know what value types are, I'd recommend googling them.
Cups
| Column |
Value Type |
Description
|
| ID |
Integer |
Unique Identifier
|
| Name |
String |
Name of cup
|
| Season |
String |
Winter, Spring, Summer, Autumn
|
| Year |
Integer |
2011, 2012, 2013, etc
|
| Type |
Integer |
1 = 4CC Elite, 2 = 4CC Babby, 3 = 4CC Qualifier, 4 = 4CC Friendly, etc
|
| Start |
Date |
First day of cup
|
| End |
Date |
Last day of cup
|
| RankPoints |
Integer |
Maximum points attainable, used for the Coefficient ranking system
|
Players
| Column |
Value Type |
Description
|
| ID |
Integer |
Unique Identifier
|
| CupID |
Integer |
What cup this player participated in (The ID from the Cups table)
|
| Team |
String |
The board this player was on (No slashes)
|
| Name |
String |
Player name, as listed on the wiki
|
| Medal |
String |
Blank, Bronze, Silver, or Gold
|
| Captain |
Bit |
Whether or not they are the team's captain
|
| RegPos |
String |
Their listed position on the wiki
|
| ShirtNumber |
Integer |
pretty obvious
|
| Starting |
Bit |
Whether or not they are on the starting 11 as listed on the wiki
|
| Link |
Integer |
The link ID that links the same player across different cups, more on that in the next table.
|
PlayerLinks
Players don't always keep the same name as the seasons go by, and because of that, a table is required that links them together. For example, Dan Schneider for /tv/'s record in the PlayerLinks table is 147, that means every record of Dan Schneider in the Players table, whether it be him as Dan the Candyman Schneider, or Dan "The Hymen Divider" Schneider should have a Link value of 147 as well. Note that this database does not track name changes within cups and only refers to the one listed on the wiki.
| Column |
Value Type |
Description
|
| ID |
Integer |
Unique Identifier
|
| Team |
String |
The board this player was on (No slashes)
|
| Name |
String |
The general player name
|
Matches
| Column |
Value Type |
Description
|
| ID |
Integer |
Unique Identifier
|
| CupID |
Integer |
What cup this match was part of (ID from the Cups Table)
|
| Round |
String |
Group A, Group B, Playoffs, Quarter Finals, etc
|
| HomeTeam |
String |
Again, no slashes
|
| AwayTeam |
String |
See above
|
| WinningTeam |
String |
The team that won, or draw otherwise (For normalization's sake, it'd be better to state whether it was the home team or away that won, but that'd require way more effort to decipher)
|
| End |
Integer |
How the match ended, 1 = Regular time, 2 = Extra time, 3 = Benaldies :DDDD
|
| UTCTime |
DateTime |
Match date as listed on the wiki
|
| Attendance |
Integer |
Max number of viewers for that match
|
| Stadium |
String |
Name of the stadium that this was played on (This can also be normalized, which would involve creating a new table for stadiums and linking them by IDs, debatable)
|
| Official |
Bit |
Whether or not this match can be used for official stats
|
| Complete |
Bit |
Meta tag used to see what records are verified and complete.
|
MatchStats
I did not include every stat recorded, but it's pretty much what you see on the main scorecard.
| Column |
Value Type |
Description
|
| ID |
Integer |
Unique Identifier
|
| MatchID |
Integer |
What match this was part of (ID from the Matches Table)
|
| Half |
Integer |
What half it is, 1 = 1st half, 2 = 2nd half, 3 = end of extra time
|
| Home |
Bit |
Whether or not this is the home team's stats or the away team's
|
| Final |
Bit |
Whether or not this is the final half of the game (This column is not required and information can be obtained from the End value in the Matches table)
|
| Poss |
Integer |
Possession
|
| Shots |
Integer |
Shots made
|
| ShotsOT |
Integer |
Shots on target
|
| Etc |
Integer |
Etc
|
Events
| Column |
Value Type |
Description
|
| ID |
Integer |
Unique Identifier
|
| MatchID |
Integer |
What match this was part of (ID from the Matches Table)
|
| PlayerID |
Integer |
What player performed this event (ID from the Players table)
|
| Type |
Integer |
1 = Goal, 2 = Assist, 3 = Own Goal, 4 = Penalty Goal (Not benuldies, but referee given ones), 5 = 1st Yellow card, 6 = Straight red, 7 = Injury (Did not return), 8 = 2nd Yellow/Red
|
| RegTime |
Integer |
Time of event
|
| InjTime |
Integer |
Time during injury time if applicable, -1 otherwise (There is a difference between scoring a goal at 45 and 45+0)
|
Performances
| Column |
Value Type |
Description
|
| ID |
Integer |
Unique Identifier
|
| MatchID |
Integer |
What match this was part of (ID from the Matches Table)
|
| PlayerID |
Integer |
What player this is for (ID from the Players Table)
|
| SubOn |
Integer |
What time the player came on the pitch
|
| SubOff |
Integer |
What time the player came off the pitch (Do not account for injury time)
|
| Rating |
Double |
Self explanatory
|
| Saves |
Integer |
Number of saves made, for 99% of matches, will be the same as the score card one unless 2 GKs were played and then you gotta watch the whole match to figure out who saved what
|
| MotM |
Bit |
Whether or not they were the man of the match
|
| Cond |
Integer |
Condition 1 being the purple/poorest, and 5 being red/best
|
Penalties
| Column |
Value Type |
Description
|
| ID |
Integer |
Unique Identifier
|
| MatchID |
Integer |
What match this was part of (ID from the Matches Table)
|
| PlayerID |
Integer |
What player this is for (ID from the Players Table)
|
| Goal |
Bit |
Whether or not they scored
|
Relating these tables together
Because each table has a unique ID and tables have columns to refer to the other table's unique IDs, we can link these together to store information on matches.
Each cup is linked to a number of matches and players.
Each match is then linked to a number of events, performances, stats, and possibly penalties.
Each event, performance, and penalty is linked to a player.
Players are linked together.
This makes it extremely simply to make changes as you only need to make one edit, and then everything can just reference that edit. This is also a double edged sword and I will use an example. Say for example you're looking at /c/'s roster from 2013 ABC. It is incomplete and there is only Yui, ID 1543 listed. You decide to fill out that roster using information from the wiki. You enter in the information, because Ika-chan would be at the top of the roster, you change Yui's name to Ika-chan and move Yui down 10 spots to where a forward would be on the roster. What you just did was edit the name of player 1543 to Ika-chan, and now, if you go look at match information for 2013 ABC, anything previously associated with Yui will now say Ika-chan instead, which could mean Ika-chan being credited for goals, cards, performances, etc that they didn't actually do. This is very important to note because it can cause a lot of headache if other people start editing information using player 1543 as Ika-chan when she was originally Yui, because now that ID is being used for two different people at the same time and unsorting that could take a bit of time. If you still have issues understanding this, please contact me and I'll try to walk you through it better.
Maintaining the SQL
This would be the process required for each cup.
- Create a cup record (~1 minute)
- Once group stage draws are done, create records for group stage matches (~10 seconds per match)
- Group stage matches lets us know what teams are participating, so we then create player records for the rosters (~1 minute per roster if importing from the team's wikipage)
- Once rosters are done, the players are linked to previous cups if they're a returning player (~5 minutes per roster)
- When a match is complete, we can then update those records (~2-4 minutes per match)
- When group stage is complete, create records for Round of 16, and repeat until the cup is over
Using information for the wiki
Beyond having a database that can be queried for cool stats like longest spent not conceding or a list of players who never got to play in a tournament, parsers are being developed to export that information into wikitext. For those who update the cup page on gameday. They'd just need to input the information through the SQL, which should take the same time if not quicker than editing the wiki, save the information, and then export that information to wikitext and then updating the cup page with that information. Using the approximate times from the previous section, it would take about 5 hours overall to record all the data for an elite cup. 5 hours split amongst 5 people over 3 weekends is absolutely no time at all, and at least half of it can be done while the streamer is waiting for subs/timeout or doing prematch setup. And everyone benefits off that, the golden boot list is automatically generated, fantasy football can be automated, managers can update their team's pages with just a few clicks and a copy and paste.
