Re: Complex SQL

From: Marc Blum <marc_at_marcblum.de>
Date: Mon, 01 Apr 2002 13:29:39 GMT
Message-ID: <3ca85f6e.1755754@news.online.de>

On Mon, 1 Apr 2002 04:12:23 -0500, "Art Scott" <as33289898002323232_at_hotmail.com> wrote:

>Team[TeamID(pk), coach, rating]
>Player[PlayerID(pk), TeamID(fk)]
>
>Want to prevent data entry from having more than 12 players for each team.

Denormalize!

CREATE TABLE team
(
teamid number primary key,
coach varchar2(100) NOT NULL,
rating varchar2(10),

player_01 VARCHAR2(100),
player_02 VARCHAR2(100),
player_03 VARCHAR2(100),
player_04 VARCHAR2(100),
player_05 VARCHAR2(100),
player_06 VARCHAR2(100),
player_07 VARCHAR2(100),
player_08 VARCHAR2(100),
player_09 VARCHAR2(100),
player_10 VARCHAR2(100),
player_11 VARCHAR2(100),
player_12 VARCHAR2(100)

);

Your business rule is:
A team consists of 0 to 12 player.
A team may not have more than 12 player.

This structure enforces this business rule.

What I don't know:
A player may only belong to one and only one team. OR
A player can belong to none, one or many teams. OR
(Further possibilities...)

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Mon Apr 01 2002 - 07:29:39 CST