🚀 go-pugleaf

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? 

the two ways that spring to mind immediately are:

1) every time a person is selected from the eligible list, remove all
his teammates from the eligible list.  that way it's not possible to
select someone from the same team because they are no longer available.

2) keep track of the players selected.  when a new player is selected,
search the selected players list to see if there is already someone
there from the same team before validating the new selection.

> Does a database program exist that makes such
>restrictions easy to implement?

i use 4th dimension, and i would find it pretty easy to implement
something like this, but that is probably because i have been using it
for 3+ years.

>  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?

sounds interesting;  but i haven't done anything like that before...

--mike

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.