🚀 go-pugleaf

In article <1992Nov19.054912.5580@seas.gwu.edu>, willson@seas.gwu.edu
(Stephen R. Willson) writes:
|>    Monday's baseball expansion draft got me thinking about a database
|>question.  During at least the first round of the draft, no more than one
|>player from each team could be taken by Tampa or Denver.  If I for some
|>reason had setup a database of all the players eligible for the draft, how
|>could I implement this restriction.  If one player from the Boston Red Sox
|>has already been selected, how could I lock out selecting a second player
|>from the Red Sox?  Does a database program exist that makes such
|>restrictions easy to implement?  One particular application which I am
|>interested in is evaluating economic proposals which are either mutually
|>exclusive, mutually dependent, or mutually independent of other proposals.
|> Does anyone have experience with designing a database for such an
|>application?
|>

For a relational solution, create three tables: Team, Player and Contract.

Each Team record describes a team, and is indexed by TeamID.

Each Player record details a player, and is indexed by PlayerID.

The Contract table describes the relationship between Team and Player.
In SQL it might look like this:

create table Contract
	(TeamID number not null primary key
	 PlayerID number not null,
	 Draftable boolean);

If the Draftable bit is FALSE, then that player is currently under contract
to that team. If the Draftable bit is TRUE then the player is currently
under contract to that team but is eligible for the draft.

Set all Draftable fields to TRUE before start of each draft season.

The SQL-ish query

	select TeamID into :teamID
	from Contract
	where PlayerID = :playerID and Draftable = 'TRUE';

will tell you if playerID is available for draft.  If so, the statement

	update Contract
	set Draftable = 'FALSE'
	where TeamID = :teamID;

will mark the player and his team mates unavailable for further draft.
The final statement

	update Contract
	set TeamID = :newTeamID
	where TeamID = :teamID and playerID = :playerID;

will associate the player to his new team.

Surely there are other things one would want to capture in a bona fide
system such as draft round, contract history, and so forth.
---                  
Roderick Sprattling		| No job too great, no time too small
sprattli@azores.crd.ge.com	| With feet to fire and back to wall.