Databaseontwerp WK poule
Dat mag, altijd interessant. Capaciteit is inderdaad een goede, maar nu niet van toepassing voor mij. En voor uitslagen heb je inderdaad wel alle doelpunten nodig.
accommodatie
accommodatie_id
accommodatie_naam
accommodatie_opening
capaciteit
capaciteit_id
capaciteit_accommodatie
capaciteit_aantal
capaciteit_datumbegin
capaciteit_datumeind
adres
adres_id
adres_categorie
adres_wie
adres_straat
adres_huisnummer
adres_huisletter
adres_huisnummertoevoeging
adres_huisnummeraanduiding
adres_locatieomschrijving
adres_postcode
adres_plaats
adres_land
bond
bond_id
bond_naam_volledig
bond_naam_weergave
bond_landid
bond_datumoprichting
categorie
categorie_id
categorie_omschrijving
club
club_id
club_naam
club_naamsortering
club_datumoprichting
club_plaats
club_land
club_accommodatie
clubstraf
clubstraf_id
clubstraf_seizoen
clubstraf_wedstrijd
clubstraf_team
clubstraf_minpunten
clubstraf_reden
competitie
competitie_id
competitie_omschrijving
competitie_bondid
competitie_type
competitieseizoen
competitieseizoen_id
competitieseizoen_seizoen
competitieseizoen_competitie
contact
contact_id
contact_categoriewat
contact_categoriewie
contact_wie
contact_omschrijving
contact_geldig
contract
contract_id
contract_persoon
contract_club
contract_begindatum
contract_einddatum
contract_huur
foto
foto_id
foto_wie
foto_url
gebeurtenis
gebeurtenis_id
gebeurtenis_wedstrijdid
gebeurtenis_gebeurtenistypeid
gebeurtenis_positieid
gebeurtenis_teamid
gebeurtenis_persoon1
gebeurtenis_persoon2
gebeurtenis_minuut
gebeurtenis_volgorde
gebeurtenis_scorethuis
gebeurtenis_scoreuit
gebeurtenistype
gebeurtenistype_id
gebeurtenistype_omschrijving
indeling
indeling_id
indeling_seizoenid
indeling_teamid
indeling_positie
indeling_voorkeurstijd
land
land_id
land_naam
land_isonummer
land_iso2
land_iso3
land_fifa
land_ioc
logo
logo_id
logo_wat
logo_wie
logo_bestandsnaam
nationaliteit
nationaliteit_id
nationaliteit_persoon
nationaliteit_land
persoon
persoon_id
persoon_naam
persoon_voorvoegsel
persoon_voornaam
persoon_geboortedatum
persoon_geboorteplaats
persoon_landid
persoon_overlijdensdatum
positie
positie_id
positie_omschrijving
positie_afkorting
positie_afbeeldingid
seizoen
seizoen_id
seizoen_omschrijving
seizoen_datumbegin
seizoen_datumeind
selectie
selectie_id
selectie_seizoen
selectie_team
selectie_persoon
team
team_id
team_club
team_elftal
toto
toto_id
toto_omschrijving
totowedstrijd
totowedstrijd_id
totowedstrijd_wedstrijd
totowedstrijd_toto
uitslag
uitslag_id
uitslag_wedstrijd
uitslag_thuis_45
uitslag_uit_45
uitslag_thuis_90
uitslag_uit_90
uitslag_thuis_105
uitslag_uit_105
uitslag_thuis_120
uitslag_uit_120
uitslag_thuis_penalty
uitslag_uit_penalty
voorspelling
voorspelling_id
voorspelling_speler
voorspelling_totowedstrijd
voorspelling_thuis
voorspelling_uit
wedstrijd
wedstrijd_id
wedstrijd_soort
wedstrijd_seizoen
wedstrijd_ronde
wedstrijd_datum
wedstrijd_accommodatie
wedstrijd_thuisteam
wedstrijd_uitteam
wedstrijd_toeschouwers
wedstrijd_afgelast
wedstrijdsoort
wedstrijdsoort_id
wedstrijdsoort_omschrijving
Gewijzigd op 14/06/2012 10:13:58 door Jeroen Jansen
Waarom een aparte tabel voor de uitslagen, dat is een overbodige 1 op 1 relatie met de wedstrijden.
@Ger Dat heb ik gedaan omdat niet elke wedstrijd een uitslag heeft (nog niet gespeeld, afgelast, uitgesteld) en ik het dan handiger vind om het in een aparte tabel te hebben staan (ook al is het dan misschien niet nodig)
Maar dan kan je die uitslag nog verder normaliseren, waardoor het zelfs nog een beetje nut heeft. Nu heb je uitslag 45, 90, 115, 120, penalties staan. Als je nu een extra veld hebt (zeg minuten) dan heb je alleen nog uit en thuis nodig en kan je meerdere records per wedstrijd hebben. Nu heb je voor verreweg de meeste wedstrijden een aantal lege velden (115, 120, penalties komen niet vaak voor).
Dat vermoeden had ik al maar strikt genomen is 'handiger' geen goed argument voor een 1 op 1 relatie tussen 2 tabellen. Het punt is dat je een extra join in de query krijgt, en dat kost perfomance.
score
score_id
score_wat
score_wedstrijd
score_thuis
score_uit
scoretype
scoretype_id
score_omschrijving
waarbij ik in scoretype dan 45, 90, 105, 120 en penalties heb opgenomen
Gewijzigd op 15/06/2012 10:37:08 door Jeroen Jansen
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
(
SELECT
COUNT(matches.match_id)
FROM
matches
WHERE(
matches.team_id_home = t.team_id
AND (
SELECT COUNT(goals.goal_id)
FROM
goals
WHERE
matches.match_id = goals.match_id
AND
goals.team_id = matches.team_id_home
) > (
SELECT
COUNT(goals.goal_id)
FROM
goals
WHERE
matches.match_id = goals.match_id
AND
goals.team_id = matches.team_id_away
)
) OR (
matches.team_id_away = t.team_id
AND (
SELECT
COUNT(goals.goal_id)
FROM
goals
WHERE
matches.match_id = goals.match_id
AND
goals.team_id = matches.team_id_home
) < (
SELECT
COUNT(goals.goal_id)
FROM
goals
WHERE
matches.match_id = goals.match_id
AND
goals.team_id = matches.team_id_away
)
)
) AS gewonnen_wedstrijden
SELECT
COUNT(matches.match_id)
FROM
matches
WHERE(
matches.team_id_home = t.team_id
AND (
SELECT COUNT(goals.goal_id)
FROM
goals
WHERE
matches.match_id = goals.match_id
AND
goals.team_id = matches.team_id_home
) > (
SELECT
COUNT(goals.goal_id)
FROM
goals
WHERE
matches.match_id = goals.match_id
AND
goals.team_id = matches.team_id_away
)
) OR (
matches.team_id_away = t.team_id
AND (
SELECT
COUNT(goals.goal_id)
FROM
goals
WHERE
matches.match_id = goals.match_id
AND
goals.team_id = matches.team_id_home
) < (
SELECT
COUNT(goals.goal_id)
FROM
goals
WHERE
matches.match_id = goals.match_id
AND
goals.team_id = matches.team_id_away
)
)
) AS gewonnen_wedstrijden
Gewijzigd op 15/06/2012 14:20:51 door gerhard l
Nu wil je het aantal gelijke spelen bepalen hoe maak jij het verschil tussen een 0-0 wedstrijd en een wedstrijd die nog niet is gespeeld?
Overigens zal bovenstaande query een foutmelding opleveren want je bent vergeten te joinen.
Ik haal eerst (per team van de poule) alle wedstrijden in de betreffende poule op. Vervolgens loop ik de wedstrijden door en verhoog per wedstrijd de tellers voor winst/verlies/gelijk (afhankelijk van resultaat) en ook het doelsaldo houdt ik dan bij.
Deze schrijf ik weg in een array die ik vervolgens sorteer op punten, wedstrijden, doelsaldo en naam.
Maar omdat een wedstrijd natuurlijk ook gestaakt kan worden, of langer doorgaat dan gepland zou ik denk ik beter een veld bij matches kunnen maken met finished 0 of 1.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SELECT t.team_id, t.team_name,
SUM(m.points) total,
SUM(m.goals_scored) gls_for,
SUM(m.goals_against) gls_against, COUNT(*) matchesplayed,
SUM(m.won) matches_won, SUM(m.draw) matches_draw,
SUM(m.lost) matches_lost
FROM teams t
LEFT JOIN
(SELECT match_id, home_id team_id
(CASE WHEN home_score > away_score THEN 3
WHEN home_score = away_score THEN 1
ELSE 0 END) points,
IF(home_score > away_score, 1, 0) won,
IF(home_score = away_score, 1, 0) draw,
IF(home_score < away_score, 1, 0) lost,
home_score goals_scored,
away_score goals_against
FROM matches
WHERE home_score IS NOT NULL
UNION
SELECT match_id, away_id team_id
(CASE WHEN home_score < away_score THEN 3
WHEN home_score = away_score THEN 1
ELSE 0 END) points,
IF(home_score < away_score, 1, 0) won,
IF(home_score = away_score, 1, 0) draw,
IF(home_score > away_score, 1, 0) lost,
away_score goals_scored, home_score goals_against
FROM matches
WHERE home_score IS NOT NULL)
AS m ON t.team_id = m.team_id
GROUP BY t.team_id, t.team_name
ORDER BY total DESC, matches_played, (gls_for - gls_against) DESC, t.teamname
SUM(m.points) total,
SUM(m.goals_scored) gls_for,
SUM(m.goals_against) gls_against, COUNT(*) matchesplayed,
SUM(m.won) matches_won, SUM(m.draw) matches_draw,
SUM(m.lost) matches_lost
FROM teams t
LEFT JOIN
(SELECT match_id, home_id team_id
(CASE WHEN home_score > away_score THEN 3
WHEN home_score = away_score THEN 1
ELSE 0 END) points,
IF(home_score > away_score, 1, 0) won,
IF(home_score = away_score, 1, 0) draw,
IF(home_score < away_score, 1, 0) lost,
home_score goals_scored,
away_score goals_against
FROM matches
WHERE home_score IS NOT NULL
UNION
SELECT match_id, away_id team_id
(CASE WHEN home_score < away_score THEN 3
WHEN home_score = away_score THEN 1
ELSE 0 END) points,
IF(home_score < away_score, 1, 0) won,
IF(home_score = away_score, 1, 0) draw,
IF(home_score > away_score, 1, 0) lost,
away_score goals_scored, home_score goals_against
FROM matches
WHERE home_score IS NOT NULL)
AS m ON t.team_id = m.team_id
GROUP BY t.team_id, t.team_name
ORDER BY total DESC, matches_played, (gls_for - gls_against) DESC, t.teamname
Lijkt mij toch een stuk eenvoudiger dan gaan lopen hannessen in PHP en dan ook een x aantal queries te veel uitvoeren.
Gewijzigd op 16/06/2012 10:38:31 door Ger van Steenderen
@Ger bedankt voor dit voorbeeld, ik kende een aantal mysql functies die je hier gebruikt nog niet, maar die ga ik nu uittesten!
Ik heb de query trouwens wat aangepast zodat ook het aantal gewonnen, gelijke en verloren spelen bepaald worden.
Gewijzigd op 16/06/2012 09:13:26 door Ger van Steenderen