COUNTRY table

Table nameCOUNTRY
DefinitionCountry of game tournament where the event takes place
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
idunique identifierchar(2)PK
namecountry namenvarchar(20)NOT NULL
PKid
FK-

COUNTRY - sample table data

idFI
nameFinland

CITY table

Table nameCITY
DefinitionCity or town of game tournament where the event takes place
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
postalcodeunique identifier (postal code)varchar(10)PK
country_idunique identifier of a countrychar(2)PK, FK1
nametown or city name, municapilitynvarchar(30)NOT NULL
PKpostalcode, country_id
FKcountry_id (COUNTRY.id)

CITY - sample table data

postalcode20780
country_idFI
nameKaarina

TEAM table

Table nameTEAM
DefinitionTournament team information
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
idunique identifierchar(4)PK
nameteam namenvarchar(50)NOT NULL
emailteam's public email addressnvarchar(30)NOT NULL
phoneteam's public phone numbervarchar(25)NULL
postalcodeteam's postal codevarchar(10)FK1, NULL
streetteam's public street addressnvarchar(80)NULL
housenumberhouse number of team's public addressnvarchar(7)NULL
IBANteam's bank account (IBAN)varchar(30)NULL
SWIFT_bankSWIFT identifier of team's bankvarchar(15)NULL
SWIFT_numberTeam's bank account numbervarchar(20)NULL
PKid
FKpostalcode (CITY.postalcode)

TEAM - sample table data

idJ001
nameRollihaukat
emailrollihaukat@gmail.com
phone+358554319543
postalcode20540
streetPaavinkatu
housenumber4C
IBANFI4870716142809720
SWIFT_bank
SWIFT_number

PLAYER table

Table namePLAYER
DefinitionPlayer's information
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
idunique identifierchar(4)PK
team_idunique identief of player's teamchar(4)FK1, NOT NULL
firstnameplayer's first namenvarchar(30)NOT NULL
lastnameplayer's surnamenvarchar(30)NOT NULL
emailplayer's email addressnvarchar(30)NULL
phoneplayer's phone numbervarchar(25)NULL
postalcodeplayer's postal codevarchar(10)FK2, NULL
streetplayer's street addressnvarchar(80)NULL
housenumberplayer's house numbernvarchar(7)NULL
PKid
FKteam_id (TEAM.id), postalcode (CITY.postalcode)

PLAYER - sample table data

idP001
team_idJ001
firstnameAnita
lastnameTilhonen
emailanita.tilhonen@gmail.com
phone+3584634151416
postalcode20780
streetHoviherrankatu
housenumber2A 5

ORGANIZER_ORG table

Table nameORGANIZER_ORG
DefinitionTournament organizer's information
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
idunique identifierchar(4)PK
nameorganizer namenvarchar(50)NOT NULL
emailorganizer's public emailnvarchar(30)NULL
phoneorganizer's phone numbervarchar(25)NULL
postalcodeorganizer's postal codevarchar(10)FK1, NULL
streetorganizer's street addressnvarchar(80)NULL
housenumberorganizer's house numbernvarchar(7)NULL
PKid
FKpostalcode (CITY.postalcode)

ORGANIZER_ORG - sample table data

idO001
namePeliturnarit
emailetunimi.sukunimi@peliturnarit.fi
phone+3585043239534
postalcode00750
streetKeskitie
housenumber20

ORG_EMPLOYEE table

Table nameORG_EMPLOYEE
DefinitionTournament organizer's employee information
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
idunique identifierchar(4)PK
organizer_org_idtournament organizer's unique identifierchar(4)FK1, NOT NULL
firstnameemployee's first namenvarchar(30)NOT NULL
lastnameemployee's surnamenvarchar(30)NOT NULL
emailemployee's email addressnvarchar(30)NULL
phoneemployee's phone numbervarchar(25)NULL
postalcodeemployee's postal codevarchar(10)FK2, NULL
streetemployee's street addressnvarchar(80)NULL
housenumberemployee's house numbernvarchar(7)NULL
PKid
FKpostalcode (CITY.postalcode), organizer_org_id (ORGANIZER_ORG.id)

ORG_EMPLOYEE - sample table data

idT001
organizer_org_idO001
firstnameHeikki
lastnameIsomaa
emailheikki.isomaa@peliturnarit.fi
phone+3584634176571
postalcode00880
streetTulppatie
housenumber7E 4

CLUBROOM table

Table nameCLUBROOM
DefinitionPhysical place of a game tournament
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
idunique identifierchar(4)PK
nameclubroom's namenvarchar(50)NOT NULL
postalcodeclubroom's postal codevarchar(10)FK1, NULL
streetclubroom's street addressnvarchar(80)NULL
housenumberclubroom's house numbernvarchar(7)NULL
PKid
FKpostalcode (CITY.postalcode)

CLUBROOM - sample table data

idLAUT
nameLautakomero
postalcode00510
streetKajaaninkatu
housenumber2

EVENT table

Table nameEVENT
DefinitionGame tournament information
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
idunique identifierchar(4)PK
clubroom_idclubroom's unique identifierchar(4)FK1, NULL
nameevent namenvarchar(50)NOT NULL
startimeevent's opening timedatetimeNULL
endtimeevent's closing timedatetimeNULL
event_feeevent's fee (payment)intNULL
extrainfoAdditional event informationnvarchar(500)NULL
PKid
FKclubroom_id (CLUBROOM.id)

EVENT - sample table data

idT001
clubroom_idLAUT
nameDnD Rogue Challenge
startime2019-11-23 14:00:00.000
endtime2019-11-24 06:00:00.000
event_fee15
lisatietojaCome to see world's top teams challenging each other.

RESERVATION table

Table nameRESERVATION
DefinitionClubroom reservation information by an organizer
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
organizer_org_idorganizer's unique identifierchar(4)PK, FK1
clubroom_idclubroom's unique identifierchar(4)PK, FK2
timeclubroom reservation timedatetimeNULL
PKorganizer_org_id, clubroom_id
FKorganizer_org_id (ORGANIZER.id), clubroom_id (CLUBROOM.id)

RESERVATION - sample table data

organizer_org_idO001
clubroom_idLAUT
time2019-11-16 12:44:23.000

ARRANGEMENT table

Table nameARRANGEMENT
DefinitionJoined information of tournament organizer and the event itself
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
organizer_org_idorganizer's unique identifierchar(4)PK, FK1
event_idevent's unique identifierchar(4)PK, FK2
PKorganizer_org_id, event_id
FKorganizer_org_id (ORGANIZER_ORG.id), event_id (EVENT.id)

ARRANGEMENT - sample table data

organizer_org_idO001
event_idT001

TEAM_STATUS table

Table nameTEAM_STATUS
DefinitionStatus of a participating team
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
team_idteam's unique identifierchar(4)PK, FK1
event_idevent's unique identifierchar(4)PK, FK2
team_statusteam's current status in the eventvarchar(20) (values: playing, registered, played, ranked)NOT NULL
rankteam's rank in the eventintNULL
PKteam_id, event_id
FKteam_id (TEAM.id), event_id (EVENT.id)

TEAM_STATUS - sample table data

team_idJ001
event_idT001
team_statusranked
rank3

PLAYER_RANK table

Table namePLAYER_RANK
DefinitionPlayer's internal rank in a team
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
player_idplayer's unique identifierchar(4)PK, FK1
team_idteam's unique identifierchar(4)PK, FK2
rankplayer's team rankintNOT NULL
PKplayer_id, team_id
FKplayer_id (PLAYER.id), team_id (TEAM.id)

PLAYER_RANK - sample table data

player_idP001
team_idJ001
rank2

TEAM_RANK table

Table nameTEAM_RANK
DefinitionRanking between tournament teams
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
rankunique identifier (rank)intPK, UNIQUE
team_idteam's unique identifierchar(4)PK, FK1, UNIQUE
PKrank, team_id
FKteam_id (TEAM.id)

TEAM_RANK - sample table data

rank432
team_idJ001

BETTOR table

Table nameBETTOR
DefinitionInformation about a person who bets on a team in an event
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
idunique identifier (account/nickname)nvarchar(20)PK
firstnamebettor's first namenvarchar(30)NOT NULL
lastnamebettor's surnamenvarchar(30)NOT NULL
emailbettor's email addressnvarchar(30)NOT NULL
phonebettor's phone numbervarchar(25)NULL
IBANbettor's bank account (IBAN)varchar(30)NULL
SWIFT_bankIdentifier or bettor's bankvarchar(15)NULL
SWIFT_numberBettor's bank account numbervarchar(20)NULL
passwordBettor's salted and encrypted password hashchar(128)NOT NULL
PKid
FK-

BETTOR - sample table data

idForceWeaver
firstnameNiko
lastnameTepponen
emailforceweaver77@hotmail.com
phone0445321770
IBANFI7176990982569721
SWIFT_bank
SWIFT_number
passwordd9bb16f24a9d35a57b6dec1603e9d12780dce7116d160a743b6fee5cdcb4ed70c494b6b838b7b5bff286079f19f6295d27fef573883eae6e9ec1d604d79b4ec7

BETTING table

Table nameBETTING
DefinitionBetting event where a bettor makes a guess for team's ranking in a tournament, and sets money and multiplier for this team
FeaturesAttributeDefinitionData typePK/FK/NOT NULL
bettor_idbettor's unique identifier (account/nickname)nvarchar(20)PK, FK1
event_idbettor's target eventchar(4)PK, FK2
team_idbettor's target teamchar(4)PK, FK3
team_rank_guessbettor's ranking guess for his/her team in an eventint (range: >= 1 ja <= 99)NOT NULL
multiplierbettor's monetary winning multiplierdecimal(4,2) (range: >= 0,01 ja <= 10,00)NOT NULL
initmoneybettor's initial money for bettingdecimal(5,2) (range: >= 0,01 and <= 999,00)NOT NULL
betstatuspersonal betting status of a bettornvarchar(20) (values queued for approval, accepted, rejected, cancelled, no win, won, other)NOT NULL
PKbettor_id, team_id, event_id
FKbettor_id (BETTOR.id), team_id (TEAM.id), event_id (EVENT.id)

BETTING - sample table data

bettor_idForceWeaver
event_idT001
team_idJ001
team_rank_guess4
multiplier2.55
initmoney30.50
betstatusno win