title: Database design advice (specific example- Fantasy Football)
body: <p>I have a database for a fantasy football competition.
  There are three schemas:</p>
  
  <ul>
  <li>afl (relating to the Australian Football League entities)</li>
  <li>ddhp (relating to the local fantasy competition entities) and</li>
  <li>dbo (for entities not specifically owned by either).</li>
  </ul>
  
  <p>Description of Entities</p>
  
  <ul>
  <li>Each player plays for an AFL Team (there's no need to track this by time, so I just record the current team the player plays for, and update this if the player changes clubs).</li>
  <li>Some players play for a ddhp Team. This is expressed by a Contract which has a FromRound and a ToRound, expressing the time boundaries for which the contract is valid.</li>
  <li>There are Rounds. There's basically no difference between an afl and a ddhp round, so there's just one table.</li>
  <li>There are Fixtures, which represent two ddhp teams playing against each other in a round.</li>
  <li>When a player plays in a round, they record Stats.</li>
  <li>Each round, each ddhp Team selects from their contracted players those who will play for them that round. This is represented by RoundPlayers.</li>
  </ul>
  
  <p>The problem is that there's a level of awkwardness between RoundPlayers and Stats. Logically, a RoundPlayer has a row in Stats to represent their playing in that round (if they played). This is a one-to-one relationship, but both tables are optional. A Player might not play in a round and therefore have no Stats. A player might not also be selected as a RoundPlayer and therefore have no row in that table. One is keyed by Round and PlayerId and the other by Round and ContractId. Navigating from one to the other is a bit awkward, especially when trying to use an ORM (eg Entity Framework 4) as the navigation properties are based on Foreign Keys, while this relationship goes via an intermediate table (Contract) to get the PlayerId.</p>
  
  <p>I've thought about adding the ContractId to Stats (if a player was chosen to play that round) but that felt wrong. I also thought about removing the 1-to-1 relationship between Stat and Roundplayer and moving them to a single table, but this felt wrong also.</p>
  
  <p>I'd appreciate any thoughts as to how to get a better relationship between RoundPlayer and Stat or even any ideas which might change this structure entirely.</p>
  
  <p><img src="https://i.stack.imgur.com/CNjhL.png" alt="enter image description here"></p>