Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The natural inclination is to look at a bracket in the order the games are played. You read the traditional diagram from the outside in. But let's think of it the other way around. Each game is played between two teams. One wins, the other loses.</p> <p>Now, there's a bit more to it than just this. The winners of a particular pair of games face off against each other in another game. So there's also a relationship between the games themselves, irrespective of who's playing in those games. That is, the teams that face off in each game (except in the first round) are the winners of two earlier games.</p> <p>So you might notice that each game has two "child games" that precede it and determine who faces off in that game. This sounds exactly like a binary tree: each root node has at most two child nodes. If you know who wins each game, you can easily determine the teams in the "parent" games.</p> <p>So, to design a database to model this, you really only need two entities: <code>Team</code> and <code>Game</code>. Each <code>Game</code> has two foreign keys that relate to other <code>Game</code>s. The names don't matter, but we would model them as separate keys to enforce the requirement that each game have no more than two preceding games. Let's call them <code>leftGame</code> and <code>rightGame</code>, to keep with the binary tree nomenclature. Similarly, we should have a key called <code>parentGame</code> that tracks the reverse relationship.</p> <p>Also, as I noted earlier, you can easily determine the teams that face off in each game by looking at who won the two preceding games. So you really only need to track the winner of each game. So, give the <code>Game</code> entity a <code>winner</code> foreign key to the <code>Team</code> table.</p> <p>Now, there's the small matter of seeding the bracket. That is, modeling the match-ups for the first round games. You could model this by having a <code>Game</code> for each team in the overall competition where that team is the <code>winner</code> and has no preceding games.</p> <p>So, the overall schema would be:</p> <pre><code>Game: winner: Team leftGame: Game rightGame: Game parentGame: Game other attributes as you see fit Team: name other attributes as you see fit </code></pre> <p>Of course, you would add all the other information you'd want to the entities: location, scores, outcome (in case the game was won by forfeit or some other out of the ordinary condition).</p>
    singulars
    1. This table or related slice is empty.
    1. POBest Schema to represent NCAA Basketball Bracket
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USAlex
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload