SitePoint Sponsor

User Tag List

Page 1 of 3 123 LastLast
Results 1 to 25 of 58
  1. #1
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database design query for Golf statistics site

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh and I add a PRIMARY KEY to the fp table like you suggest.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    finishing position for Happy Gilmore (golferID 937) for the last 5 US tour events --
    Code:
    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 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937 thats really helpful. I'll get stuck into it this weekend.

    Cheers

  7. #7
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by 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."
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 :-)

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    please be kind enough to run the following:
    Code:
    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
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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.
    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.

  12. #12
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  13. #13
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  16. #16
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    both of those queries produce the exact same execution path

    they are semantically equivalent, and differ only in syntax
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    > 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

  21. #21
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  22. #22
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  25. #25
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Bristol
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •