StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Looks like you need something similar to this:</p> <p><img src="https://i.stack.imgur.com/TRiae.png" alt="enter image description here"></p> <p>Out of all the possible position "types" (<code>Position</code>) we build a set of positions that exist in the specific team (<code>TeamPosition</code>) and identify the person that fills each position (<code>TeamPosition.PersonId</code><sup>1</sup>). </p> <p>The head is represented by the "reverse" foreign key FK2 in <code>Team</code><sup>2</sup>. Unlike a boolean flag, this naturally ensures there cannot be more than one head position per team.</p> <p>This model also allows different teams to be headed by different types of positions: for example one team might be headed by a "head of marketing" while the other is headed by a "senior technical officer".</p> <p>It is still possible to have the same person fulfill multiple positions (including head positions), which is compatible with your requirements, as far as I understand. And if that's true, then I don't really see a problem in showing the same person as a member of multiple teams in the UI. Alternatively, you could designate one of the person's position's as "primary" (using a "reverse" FK similar to above) and then just show the primary position and a "More..." button beside it (or similar).</p> <hr> <p><em><sup>1</sup> Make it NOT NULL if there cannot be a vacant team position. If the same position can exist multiple times per team, either move the <code>PersonId</code> to <code>TeamPosition</code> PK, or add a new field <code>PositionNo</code> to the PK. If the same person cannot have multiple positions inside the same team, add an alternate key on <code>{TeamId, PersonId}</code>.</em></p> <p><em><sup>2</sup> Unfortunately, MS SQL Server is a bit more squeamish than some other DBMSes, and will refuse to do referential actions (such as ON DELETE CASCADE) on circular references like this. If you need referential actions, implement them via INSTEAD OF triggers.</em></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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