Database design query for Golf statistics site

[daddyg]

Hi,

I'm building a database of golf tournament finishing position statistics for my golf betting website. The idea is to have two pages of stats, one showing the golfer finishing positions for the last 5 US PGA tournaments and another for the last 5 European Tour events.

As this is my first stab at mysql/php I'd be grateful for feedback on the following database design:-

tbl_golfers (holds the names of all the golfers)
ID smallint UNSIGNED Not Null Auto Increment PRIMARY
Golfer varchar(40) UNIQUE

tbl_tournaments_Euro (holds European Tournament names and dates)
ID smallint UNSIGNED Not Null Auto Increment PRIMARY
Tournament varchar(100) Not Null
T_Date date Not Null INDEX

tbl_tournaments_US (holds US Tournament names and dates)
ID smallint UNSIGNED Not Null Auto Increment PRIMARY
Tournament varchar(100) Not Null
T_Date date Not Null INDEX

tbl_fp_Euro (holds golfer finishing positions for European tournaments)
GolferID smallint UNSIGNED Not Null INDEX
TournamentID smallint UNSIGNED Not Null INDEX
Position varchar(10) Null

tbl_fp_US (holds golfer finishing positions for US tournamnts)
GolferID smallint UNSIGNED Not Null INDEX
TournamentID smallint UNSIGNED Not Null INDEX
Position varchar(10) Null

A few questions:
1) Does this design look ok generally or are there better ways of doing it?

2) Is it ok to not have a PRIMARY KEY for the finishing positions tables?

3) Do the Keys I have assigned look ok?

4) What SQL query would I need to run to pull the finishing position stats from my database so that I can display each golfers finish for the last 5 tournaments of say the US Tour? This is what I'd want to see on my webpage:-

Golfer TName1 TName2 TName3 TName4 TName5
Sergio Garcia 27 10 Missed 42 Tied 3rd
Cut


Ernie Els 7 32 Tied 2nd 9 0


Vijay Singh 1 17 5 42 Missed
Cut


etc etc....


The 0 under TName5 for Ernie Els would denote a tournament he didn't play in ...would this appear automatically if no data was inserted into the finishing positions table(s) for a particular player/tournament combination, ie is that what the NULL is for in the Position field in the finishing position tables?

Hope this made sense and thanks for any advice you can offer.

[r937]

1) Does this design look ok generally or are there better ways of doing it?

i would combine the two tournament tables, with a "type" code to distinguish between euro and american tournaments


2) Is it ok to not have a PRIMARY KEY for the finishing positions tables?

yes but it's better to have one


3) Do the Keys I have assigned look ok?

yes, but add a pk to the finishing positions table

tbl_fp (holds golfer finishing positions for any tournament)
GolferID smallint UNSIGNED Not Null INDEX
TournamentID smallint UNSIGNED Not Null INDEX
Position varchar(10) Null
primary key (GolferID,TournamentID)


4) What SQL query would I need to run to pull the finishing position stats from my database so that I can display each golfers finish for the last 5 tournaments of say the US Tour?

depends on if you agree to merge the two tournament tables or not

[daddyg]

Thanks for the lightning quick response

So one tournament table with a varchar field for the type code called say "Tour" which accepts either Euro or US

and also just one finishing positions table yes.

What would the SQL query be then to display finishing positions for the last 5 US tour events?

Thanks again

[daddyg]

Oh and I add a PRIMARY KEY to the fp table like you suggest.

[r937]

finishing position for Happy Gilmore (golferID 937) for the last 5 US tour events --

select F.Position
     , T.Tournament
     , T.T_Date
  from tbl_tournaments as T
left outer
  join tbl_fp as F
    on T.ID 
     = F.TournamentID
   and F.GolferID = 937 
 where T.Tour = 'US'
   and 5
    <= ( select count(*)
           from Tournaments
          where Tour = 'US'
            and T_Date >= T.T_Date )

[daddyg]

Thanks r937 thats really helpful. I'll get stuck into it this weekend.

Cheers

[daddyg]

My webhost has just helpfully informed me that the JOIN sql command is deactivated on their webservers coz to use their words its "very resource consuming and slow."

Are their any other commands I can use to do the same query, ie extracting the last 5 tournament finishes for a particular golfer on say the US Tour? If there are, would I also need to re jig my tabless?

Thanks again for any help you can provide.

[r937]

My webhost has just helpfully informed me that the JOIN sql command is deactivated on their webservers coz to use their words its "very resource consuming and slow."

at face value, this is absolute nonsense, and if true, shows enormous misunderstanding

first of all i know of no database where you can turn off joins

is the problem with the subquery? are you on mysql before version 4.1?

[daddyg]

What happened was I set up my tables a few days ago and started getting the following messages in myphpadmin after creating each table:-

"The additional Features for working with linked Tables have been deactivated. To find out why click here.

PMA Database ... not OK[ Documentation ]
General relation features Disabled

Various searches suggested the config_inc.php file needs amending so I emailed my webhost valuehost, and mentioned I wanted to run JOIN commands so was worried about the error msg. They replied:-

"JOIN command was deactivated indeed as it is very resource consuming and slow. However there are always ways around it - please try to adjust your code to avoid using JOIN.

We do apologize for the inconvenience caused at this time."

I've emailed them back to see if it can be reactivated but I'm not holding my breath to be honest. I'm surprised too....would have thought JOIN requests were pretty central to mysql which they push as a selling point for their hosting service.

Are they talking out of their **** then?

Thanks again r937. (that sounds like a line from a Star Wars movie :-)

[r937]

please be kind enough to run the following:

create table integers (i integer); 
insert into integers (i) values 
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
; 
create table aaargh
( id tinyint not null primary key auto_increment
, foo varchar(9)
);
insert into aaargh(foo) values
('curly'),('larry'),('moe')
;

then please run these three queries and tell me what you get for each one --

select * from integers,aaargh
;
select * from integers,aaargh where i=id
;
select * from integers inner join aaargh on i=id
;

[kuato]

My webhost has just helpfully informed me that the JOIN sql command is deactivated on their webservers coz to use their words its "very resource consuming and slow."

Are their any other commands I can use to do the same query, ie extracting the last 5 tournament finishes for a particular golfer on say the US Tour? If there are, would I also need to re jig my tabless?

Thanks again for any help you can provide.

Run far far away from that webhost. That is some of the lamest crap I've heard in a while. They should just reword it as: Due to our clueslessness we advise you to DENORMALIZE your tables so you can avoid joins altogether and save our cluessless admins the time it would take to actually setup and optimize our systems properly. Sounds like they are running this on a PII-250 with 128MB of ram and a few 5200 rpm IDE disks or something like that lol.

That just blows my mind especially in this day and age where processing power, memory and disk space is so cheap and powerful.

[daddyg]

Thanks for both your replies. I just hope I don't have to move everything over to a new webhost...the last thing I need right now. I certainly won't be using them again though for future websites.

I'll run your queries tonight r937 and let you know the results

Cheers

[daddyg]

Fyi, I've just received this reply from valuehost after I asked them to reactivate it:-

"There are many ways to replace JOIN and even increase MySQL responce -
using combined SELECT statements, temporary tables, etc.

Please see MySQL web site to get details/ examples."

I'm new to mysql so I really don't know how valid this assertion is....would appreciate any advice.

[r937]

it sounds like bafflegab

it really sounds like they don't have a clue

WTF is a "combined SELECT statement"?

temporary tables are more efficient that joins? idiotic

did you try my little script?

[kuato]

Combined SELECT?

Please run r937's test, I can't wait to see the results. If what I think your webhost is saying then the first two tests will work but not the third. This should really be interesting. If that is the case then your webhost doesn't realize that these two queries are both doing the same join:

select * from integers,aaargh where i=id
select * from integers inner join aaargh on i=id

and that the first query is nonstandard and the inner join is the ANSI standard.

OOhh I can't wait for the results. hurry hurry

[daddyg]

I'm at work but will be leaving soon so should have something for you in about 3 hours!

> If that is the case then your webhost doesn't realize that these two queries are
> both doing the same join:

> select * from integers,aaargh where i=id
> select * from integers inner join aaargh on i=id

> and that the first query is nonstandard and the inner join is the ANSI standard.

in which case there shouldn't be a reason for them not to allow joins right? In other words the first query is actually atleast as resource hungry (and possibly slower if non-standard?) as the second query using the JOIN ? Is that what you're saying?

[r937]

both of those queries produce the exact same execution path

they are semantically equivalent, and differ only in syntax

[daddyg]

And the results are...(drum roll please!):-

select * from integers,aaargh;
i 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9
id 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3
foo curly curly curly curly curly curly curly curly curly curly larry larry larry larry larry larry larry larry larry larry moe moe moe moe moe moe moe moe moe moe

select * from integers,aaargh where i=id;
i id foo
1 1 curly
2 2 larry
3 3 moe

select * from integers inner join aaargh on i=id;
i id foo
1 1 curly
2 2 larry
3 3 moe


So joins clearly do work! That being the case, why would I get that "The additional Features for working with linked Tables have been deactivated. To find out why click here.

PMA Database ... not OK[ Documentation ]
General relation features Disabled"

message ?

And what on earth was the support at valuehost saying joins were deactivated for? It will be interesting to hear their response when I email them.

Should I assume everything is hunky dory and go ahead with my golf stats database design now?

Once again, many thanks for all the brilliant help I've received here guys.

[r937]

hmmm, i wonder if "linking" means setting up foreign keys

the message came from phpmyadmin?

see, in mysql the way you have foreign keys work is to declare them in innodb tables, and maybe your host has decided (why, i have no idea) to disable innodb tables in phpmyadmin

if that is true, then your host is still hopelessly clueless if he or she told you that joins are disallowed, because clearly they aren't

maybe linking tables in phpmyadmin is something that you can turn on (or off) so that when you click on a table to build a query on it, phpmyadmin will automatically "link in" the related tables into the sql and create the join syntax for you, but again, i can think of no reason why your host might think this was something to disallow

[daddyg]

> the message came from phpmyadmin?

Yes, but I've so been creating myIsam rather than innodb tables, though I have no preference. Would you recommend innodb?

I'll email valuehost tomorrow and let you know the outcome.

Cheers

[daddyg]

Actually innodb tables aren't listed in the drop down menu in phpmyadmin.

I have one more question I'm hoping you might be able to help me with:-

I've created 3 tables


tbl_golfers
ID smallint UNSIGNED NOT NULL auto increment PRIMARY
Golfer VARCHAR(40) NOT NULL INDEX


tbl_tournaments
ID smallint UNSIGNED NOT NULL auto increment PRIMARY
Tournament varchar(75) NOT NULL
T_Date date NOT NULL INDEX
Tour varchar(5) NOT NULL


tbl_finish_pos
GolferID smallint UNSIGNED NOT NULL auto increment PRIMARY
TournamentID smallint UNSIGNED NOT NULL auto increment PRIMARY
Position varchar(10) NULL


I've used a .txt file to insert all the golfers names into tbl_golfers. The text file had the golfers listed alphabetically by first name so Aaron Baddeley has been allocated
ID 1 for example.

I've also inserted the tournament details into tbl_tournaments.

I also have a .txt file for each tournament listing the finishing positions of each golfer.
It strikes me that I'm going to have to manually add to this text file the Golfer ID number for each of the Golfers for each tournament before inserting that .txt file info into tbl_finish_pos. With two tournaments a week and 140 odd golfers in each tournament this seems like it could be very time consuming.

So my question is, is there any quicker way I can get the GolferID field in tbl_finish_pos correctly filled out with the correct ID for the relevant golfer?

Thanks a lot.

[daddyg]

I should add that my finishing positions .txt file (I have one .txt file per tournament) lists the golfers by finishing position rather than first name alphabetically.

[r937]

yes, you can upload the finishing positions data into a temp table and then use joins in order to insert the right data into tbl_finish_pos

if you could show a few sample rows of the data in each table...

[daddyg]

sample data:

tbl_golfers
ID Golfer
1 Aaron Baddeley
2 Alastair Forsyth
3 Anders Hansen
4 Angel Cabrera
5 Arjun Atwal
etc

tbl_tournaments
ID Tournament T_Date Tour
1 Michelin Championship 2004-10-10 US
2 Volvo Masters Andalucia 2004-10-31 EURO
3 Chrysler Classic of Greensboro 2004-10-17 US
4 Volvo China Open 2004-11-28 EURO
5 Funai Classic 2004-10-24 US
6 Omega Hong Kong Open 2004-12-05 EURO


I don't have anything in the tbl_finish_pos yet but it should be along these lines:-
GolferID TournamentID Position
3 1 27
145 1 2
1 1 14
3 3 2
145 3 1

etc

Cheers.

[daddyg]

Sorry, the numbers for the tbl_finish_pos table havn't formatted very well....hopefully you can make it out. Each of the 3 fields takes a number although the Position field is a varchar because golfers missing the cut or getting disqualified will be given MC or DISQ respectively.

Hope that makes sense.