sql - Tournaments, Players and Match Relations -


i have multiple players in multiple tournaments in multiple seasons (tables: player, tournament, season). match between 2 players has reference specific tournament , specific season.

table match     id     season_id     tournament_id     player_home     player_away

let's natural assign players tournaments obvious benefits. one-to-many table between seasons , tournaments (call table competition) , many-to-many table competitions , players?

table competition     id     season_id     tournament_id  table competition_player     id     competition_id     player_id  table match     id     competition_id     player_home     player_away

or there more simple/better way achieve this?

thanks advice.

sqlfiddle

very basic still can see relationship. here.

structure

tables

  • players

    -------------------------- |id | name | otherinfo   | -------------------------- | 1 | john | player info | -------------------------- | 2 |  tom | player info | --------------------------

  • seasons

    ----------------------------------- |id | seasonname    | seasoninfo  | ----------------------------------- |01 | myfirstseason | season info | -----------------------------------

  • tournements 

    ------------------------------------ | id | seasonid | tournamentname   | ------------------------------------ |001 |    01    | myfirsttournament| ------------------------------------

  • encounters

    --------------------------------------- | id | firstplayerid | secondplayerid | --------------------------------------- |  |       1       |       2        |   ---------------------------------------

  • matchs

    ----------------------------------------------- | id | tournamentid | encounterid | matchinfo | ----------------------------------------------- |0001|     001      |           | info | -----------------------------------------------

thing you're using many keys nothing. need take step think design. know took out columns it's example's sake. i'm sure understand put them back. wanted show relationships between tables.

query

let's imply want basic information (once again example)

select m.id,  t.tournamentname, s.seasonname,  (select pl.name players pl   join encounters en on pl.id = en.firstplayerid   pl.id = en.firstplayerid) [firstplayername], (select pl.name players pl   join encounters en on pl.id = en.secondplayerid  pl.id = en.secondplayerid) [secondplayername], m.matchinfo  matchs m  join tournaments t on m.tournamentid = t.id join encounters e on m.encounterid = e.id join seasons s on  t.seasonid = s.id

Comments

Post a Comment

Popular posts from this blog

linux - xterm copying to CLIPBOARD using copy-selection causes automatic updating of CLIPBOARD upon mouse selection -

i've tried various ways coerce xterm (versions 285 , 292) copy selection clipboard clipboard whenever press ctrl-shift-c. promising way far has been, in ~/.xresources, put this: xterm*vt100.translations: #override \ ctrl shift <keypress> c: copy-selection(clipboard) \n\ ctrl shift <keypress> v: insert-selection(clipboard) ctrl-shift-v works perfectly, copying has nuance... if restart xterm, highlighting text puts things in primary clipboard; expected, proper, default behavior. if hit ctrl-shift-c, copies current selection clipboard clipboard. the bug, however, if highlight text after first time pressed ctrl-shift-c, you'll see highlighting copies both primary and clipboard. cannot figure out how stop xterm updating clipboard upon selection... , doesn't make sense me in first place. i said, @ specific point in time, copy selection clipboard... yet starts updating automatically upon selection after doing once... anyone have workaround...
Read more

c++ - qgraphicsview horizontal scrolling always has a vertical delta -

Image
i've subclassed qgraphicsview custom canvas used in cad application. i've reimplemented qgraphicsview::wheelevent check keyboard modifiers control key and, if control key pressed, zoom. i'm trying implement horizontal scroll when user holds shift , uses wheel. the problem i'm having horizontal scrolling always scrolls 0.279. not huge problem, hugely annoying , points else being wrong. so, here questions: is right way implement horizontal scrolling? if not, is? how eliminate delta of 0.279? thanks in advance. code , sample output below void myview::zoom(int delta) { double factor = pow(1.2, delta/abs(delta)); this->scale(factor, factor); } void myview::scrollhorizontal(int level) { qpointf center = maptoscene(viewport()->rect().center()); qdebug() << "center: " << center.x() << ", " << center.y(); centeron(qpointf(center.x() - level, center.y())); } void myview::wheelevent(qwheeleve...
Read more