[fwd] Re: [thelist] SQL and Bad Database design

[Michael Pemberton]

---------------------------------------------
This message was held by thelist software and
is being manually forwarded by a list admin.
Please remember to send emails in plain-text
format only, or they will not reach thelist
until it is later forwarded by a list admin.
---------------------------------------------


I didn't put much effort into my previous post.  sorry.  Here's the mysql
code
that will generate the table.

##############################################33
# Table structure for table 'awayplayerstats'

CREATE TABLE awayplayerstats (
   CompId varchar(6) NOT NULL,
   Round char(3) NOT NULL,
   MatchNo int(11) DEFAULT '0' NOT NULL,
   ClubId varchar(6) NOT NULL,
   PlayerId varchar(9) NOT NULL,
   Stat_Code char(3) NOT NULL,
   Stat_No int(11) DEFAULT '0' NOT NULL,
   Qtr_1 int(11) DEFAULT '0' NOT NULL,
   Qtr_2 int(11) DEFAULT '0' NOT NULL,
   Qtr_3 int(11) DEFAULT '0' NOT NULL,
   Qtr_4 int(11) DEFAULT '0' NOT NULL,
   Total bigint(20) DEFAULT '0' NOT NULL,
   PRIMARY KEY (CompId, Round, MatchNo, ClubId, PlayerId, Stat_Code),
   KEY ClubId (ClubId, PlayerId, Stat_Code, Stat_No, Qtr_1, Qtr_2, Qtr_3,
Qtr_4, Total)
);

Most of the values are useless for this task, but the main ones are
Stat_Code
and The Qtr_? totals.

There are actually 35 different stats, but I only use 5 for this page.
They
are shown in the sample output I've attached.  It also shows the problem I
am
having where the order is out in all but the first coloumn.

rudy wrote:

> >    I have one table that contains all the stats.
> >    It doesn't have each stat as a separate field.
> >    It uses two fields, "stat_id" and "stat_no".
> >    Each player can have only one record per stat,
> >         but may have up to 15 different stats.
>
> hi michael
>
> where's the player_id, same table?  can you give examples of the values
in
> stat_id and stat_no?
>
> >For example, the page requires that I extract
> >"stat1","stat2","stat3","stat4","stat5" and sort the players in that
> >order.  The problem I have at the moment is that I can't seem to get
the
> >output to sort correctly after the first column.
>
> you want the best-to-worst players for each stat_id?  based on values of
> stat_no?
>
> i'm old and slow, and often i gotta see actual data before i can
understand
> what kind of query is required...
>
> rudy
>
> ---------------------------------------
> For unsubscribe and other options, including
> the Tip Harvester and archive of TheList go to:
> http://lists.evolt.org Workers of the Web, evolt !

--
Michael Pemberton
mpember at phreaker.net
ICQ: 12107010