Re: Complex SQL

lol

This is a simply non first normal form

Do you want a good solution and evolutive one. I'll suggest the following:

Team(TeamID(pk), TeamName, ...)
Coach(CoachID(pk), CoachName, ...)
Player(PlayerID(pk), PlayerName, ...)
CurrentPlayer((PlayerID(fk), StartDate(fk), TeamID(fk))(pk)) OldPlayer((PlayerID(fk), StartDatePlayer(fk), EndDatePlayer(fk), TeamID(fk))(pk))
CurrentCoach((CoachID(fk), StartDateCoach(fk), TeamID(fk))(pk)) OldCoach((CoachID(fk), StartDateCoach(fk), EndDateCoach(fk), TeamID(fk))(pk))

You can replay to all queries with this with simple SQL and not complex SQL as stated in the object ! isn't it ?

If there is something better please i want hear from you.

@+

"Ashish Mittal" <mittalashish_at_yahoo.com> a écrit dans le message de news: rgkq8.180147$uA5.147874_at_rwcrnsc51.ops.asp.att.net...
> Do not denormalize. Imagine doing a query which finds out the coach for
all
> players in the system :)
> "Marc Blum" <marc_at_marcblum.de> wrote in message
> news:3ca85f6e.1755754_at_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 Sun Apr 07 2002 - 14:57:45 CDT