NetNews Usenet Archive 1992 #27/NN_1992_27.iso/spool/comp/sys/mac/database/1485

Internet Message Format | 1992-11-20 | 2.8 KB

Path: sparky!uunet!crdgw1!rdsunx.crd.ge.com!azores!sprattli
From: [e-mail] (Rod Sprattling)
Newsgroups: comp.sys.mac.databases
Subject: Re: Restricting selection
Message-ID: <[e-mail]>
Date: 20 Nov 92 21:28:32 GMT
References: <[e-mail]>
Sender: [e-mail] (Required for NNTP)
Reply-To: [e-mail] (Rod Sprattling)
Organization: GE Corporate R&D Center
Lines: 68
Nntp-Posting-Host: azores.crd.ge.com

In article <[e-mail]>, [e-mail]
(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
[e-mail]    | With feet to fire and back to wall.