COUNTRY table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	COUNTRY
Definition	Country of game tournament where the event takes place
	id	unique identifier	char(2)	PK
	name	country name	nvarchar(20)	NOT NULL
PK	id
FK	-

COUNTRY - sample table data

id	FI
name	Finland

CITY table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	CITY
Definition	City or town of game tournament where the event takes place
	postalcode	unique identifier (postal code)	varchar(10)	PK
	country_id	unique identifier of a country	char(2)	PK, FK1
	name	town or city name, municapility	nvarchar(30)	NOT NULL
PK	postalcode, country_id
FK	country_id (COUNTRY.id)

CITY - sample table data

postalcode	20780
country_id	FI
name	Kaarina

TEAM table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	TEAM
Definition	Tournament team information
	id	unique identifier	char(4)	PK
	name	team name	nvarchar(50)	NOT NULL
	email	team's public email address	nvarchar(30)	NOT NULL
	phone	team's public phone number	varchar(25)	NULL
	postalcode	team's postal code	varchar(10)	FK1, NULL
	street	team's public street address	nvarchar(80)	NULL
	housenumber	house number of team's public address	nvarchar(7)	NULL
	IBAN	team's bank account (IBAN)	varchar(30)	NULL
	SWIFT_bank	SWIFT identifier of team's bank	varchar(15)	NULL
	SWIFT_number	Team's bank account number	varchar(20)	NULL
PK	id
FK	postalcode (CITY.postalcode)

TEAM - sample table data

id	J001
name	Rollihaukat
email	rollihaukat@gmail.com
phone	+358554319543
postalcode	20540
street	Paavinkatu
housenumber	4C
IBAN	FI4870716142809720
SWIFT_bank
SWIFT_number

PLAYER table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	PLAYER
Definition	Player's information
	id	unique identifier	char(4)	PK
	team_id	unique identief of player's team	char(4)	FK1, NOT NULL
	firstname	player's first name	nvarchar(30)	NOT NULL
	lastname	player's surname	nvarchar(30)	NOT NULL
	email	player's email address	nvarchar(30)	NULL
	phone	player's phone number	varchar(25)	NULL
	postalcode	player's postal code	varchar(10)	FK2, NULL
	street	player's street address	nvarchar(80)	NULL
	housenumber	player's house number	nvarchar(7)	NULL
PK	id
FK	team_id (TEAM.id), postalcode (CITY.postalcode)

PLAYER - sample table data

id	P001
team_id	J001
firstname	Anita
lastname	Tilhonen
email	anita.tilhonen@gmail.com
phone	+3584634151416
postalcode	20780
street	Hoviherrankatu
housenumber	2A 5

ORGANIZER_ORG table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	ORGANIZER_ORG
Definition	Tournament organizer's information
	id	unique identifier	char(4)	PK
	name	organizer name	nvarchar(50)	NOT NULL
	email	organizer's public email	nvarchar(30)	NULL
	phone	organizer's phone number	varchar(25)	NULL
	postalcode	organizer's postal code	varchar(10)	FK1, NULL
	street	organizer's street address	nvarchar(80)	NULL
	housenumber	organizer's house number	nvarchar(7)	NULL
PK	id
FK	postalcode (CITY.postalcode)

ORGANIZER_ORG - sample table data

id	O001
name	Peliturnarit
email	etunimi.sukunimi@peliturnarit.fi
phone	+3585043239534
postalcode	00750
street	Keskitie
housenumber	20

ORG_EMPLOYEE table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	ORG_EMPLOYEE
Definition	Tournament organizer's employee information
	id	unique identifier	char(4)	PK
	organizer_org_id	tournament organizer's unique identifier	char(4)	FK1, NOT NULL
	firstname	employee's first name	nvarchar(30)	NOT NULL
	lastname	employee's surname	nvarchar(30)	NOT NULL
	email	employee's email address	nvarchar(30)	NULL
	phone	employee's phone number	varchar(25)	NULL
	postalcode	employee's postal code	varchar(10)	FK2, NULL
	street	employee's street address	nvarchar(80)	NULL
	housenumber	employee's house number	nvarchar(7)	NULL
PK	id
FK	postalcode (CITY.postalcode), organizer_org_id (ORGANIZER_ORG.id)

ORG_EMPLOYEE - sample table data

id	T001
organizer_org_id	O001
firstname	Heikki
lastname	Isomaa
email	heikki.isomaa@peliturnarit.fi
phone	+3584634176571
postalcode	00880
street	Tulppatie
housenumber	7E 4

CLUBROOM table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	CLUBROOM
Definition	Physical place of a game tournament
	id	unique identifier	char(4)	PK
	name	clubroom's name	nvarchar(50)	NOT NULL
	postalcode	clubroom's postal code	varchar(10)	FK1, NULL
	street	clubroom's street address	nvarchar(80)	NULL
	housenumber	clubroom's house number	nvarchar(7)	NULL
PK	id
FK	postalcode (CITY.postalcode)

CLUBROOM - sample table data

id	LAUT
name	Lautakomero
postalcode	00510
street	Kajaaninkatu
housenumber	2

EVENT table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	EVENT
Definition	Game tournament information
	id	unique identifier	char(4)	PK
	clubroom_id	clubroom's unique identifier	char(4)	FK1, NULL
	name	event name	nvarchar(50)	NOT NULL
	startime	event's opening time	datetime	NULL
	endtime	event's closing time	datetime	NULL
	event_fee	event's fee (payment)	int	NULL
	extrainfo	Additional event information	nvarchar(500)	NULL
PK	id
FK	clubroom_id (CLUBROOM.id)

EVENT - sample table data

id	T001
clubroom_id	LAUT
name	DnD Rogue Challenge
startime	2019-11-23 14:00:00.000
endtime	2019-11-24 06:00:00.000
event_fee	15
lisatietoja	Come to see world's top teams challenging each other.

RESERVATION table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	RESERVATION
Definition	Clubroom reservation information by an organizer
	organizer_org_id	organizer's unique identifier	char(4)	PK, FK1
	clubroom_id	clubroom's unique identifier	char(4)	PK, FK2
	time	clubroom reservation time	datetime	NULL
PK	organizer_org_id, clubroom_id
FK	organizer_org_id (ORGANIZER.id), clubroom_id (CLUBROOM.id)

RESERVATION - sample table data

organizer_org_id	O001
clubroom_id	LAUT
time	2019-11-16 12:44:23.000

ARRANGEMENT table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	ARRANGEMENT
Definition	Joined information of tournament organizer and the event itself
	organizer_org_id	organizer's unique identifier	char(4)	PK, FK1
	event_id	event's unique identifier	char(4)	PK, FK2
PK	organizer_org_id, event_id
FK	organizer_org_id (ORGANIZER_ORG.id), event_id (EVENT.id)

ARRANGEMENT - sample table data

organizer_org_id	O001
event_id	T001

TEAM_STATUS table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	TEAM_STATUS
Definition	Status of a participating team
	team_id	team's unique identifier	char(4)	PK, FK1
	event_id	event's unique identifier	char(4)	PK, FK2
	team_status	team's current status in the event	varchar(20) (values: playing, registered, played, ranked)	NOT NULL
	rank	team's rank in the event	int	NULL
PK	team_id, event_id
FK	team_id (TEAM.id), event_id (EVENT.id)

TEAM_STATUS - sample table data

team_id	J001
event_id	T001
team_status	ranked
rank	3

PLAYER_RANK table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	PLAYER_RANK
Definition	Player's internal rank in a team
	player_id	player's unique identifier	char(4)	PK, FK1
	team_id	team's unique identifier	char(4)	PK, FK2
	rank	player's team rank	int	NOT NULL
PK	player_id, team_id
FK	player_id (PLAYER.id), team_id (TEAM.id)

PLAYER_RANK - sample table data

player_id	P001
team_id	J001
rank	2

TEAM_RANK table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	TEAM_RANK
Definition	Ranking between tournament teams
	rank	unique identifier (rank)	int	PK, UNIQUE
	team_id	team's unique identifier	char(4)	PK, FK1, UNIQUE
PK	rank, team_id
FK	team_id (TEAM.id)

TEAM_RANK - sample table data

rank	432
team_id	J001

BETTOR table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	BETTOR
Definition	Information about a person who bets on a team in an event
	id	unique identifier (account/nickname)	nvarchar(20)	PK
	firstname	bettor's first name	nvarchar(30)	NOT NULL
	lastname	bettor's surname	nvarchar(30)	NOT NULL
	email	bettor's email address	nvarchar(30)	NOT NULL
	phone	bettor's phone number	varchar(25)	NULL
	IBAN	bettor's bank account (IBAN)	varchar(30)	NULL
	SWIFT_bank	Identifier or bettor's bank	varchar(15)	NULL
	SWIFT_number	Bettor's bank account number	varchar(20)	NULL
	password	Bettor's salted and encrypted password hash	char(128)	NOT NULL
PK	id
FK	-

BETTOR - sample table data

id	ForceWeaver
firstname	Niko
lastname	Tepponen
email	forceweaver77@hotmail.com
phone	0445321770
IBAN	FI7176990982569721
SWIFT_bank
SWIFT_number
password	d9bb16f24a9d35a57b6dec1603e9d12780dce7116d160a743b6fee5cdcb4ed70c494b6b838b7b5bff286079f19f6295d27fef573883eae6e9ec1d604d79b4ec7

BETTING table

Features	Attribute	Definition	Data type	PK/FK/NOT NULL
Table name	BETTING
Definition	Betting event where a bettor makes a guess for team's ranking in a tournament, and sets money and multiplier for this team
	bettor_id	bettor's unique identifier (account/nickname)	nvarchar(20)	PK, FK1
	event_id	bettor's target event	char(4)	PK, FK2
	team_id	bettor's target team	char(4)	PK, FK3
	team_rank_guess	bettor's ranking guess for his/her team in an event	int (range: >= 1 ja <= 99)	NOT NULL
	multiplier	bettor's monetary winning multiplier	decimal(4,2) (range: >= 0,01 ja <= 10,00)	NOT NULL
	initmoney	bettor's initial money for betting	decimal(5,2) (range: >= 0,01 and <= 999,00)	NOT NULL
	betstatus	personal betting status of a bettor	nvarchar(20) (values queued for approval, accepted, rejected, cancelled, no win, won, other)	NOT NULL
PK	bettor_id, team_id, event_id
FK	bettor_id (BETTOR.id), team_id (TEAM.id), event_id (EVENT.id)

BETTING - sample table data

bettor_id	ForceWeaver
event_id	T001
team_id	J001
team_rank_guess	4
multiplier	2.55
initmoney	30.50
betstatus	no win