Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Table and Design Problems

    Hey guys I was hoping someone could steer me in the right direction! I've been working on this for a while and, I won't lie, it is for a class, but, I am making it for our college too. It's basically a database to keep track of stats from meets. An example of what I want it to do is, lets say someone runs the 800m run and they run a 1:56 or something, well that person will have that stat to their name, if that makes sense. But anyway here are my tables.

    By the way, this will go into a MySQL database, and will be used from a web interface, not access. But this is what I have so far, and for some reason, it's not really making sense to me...

    Code:
    ATHLETE 
         AthleteID (text 15)
         AthleteFirstName (text 15)
         AthleteLastName (text 25)
         AthleteDOB (date)
         AthleteHome (text 25)
         AthleteHighschool (text 40)
         AthleteYear (text 2)
         AthleteGender (text 1)
         AthleteWeight (number 3)
         AthleteHeight  (number 3)
         AthleteEvents (text 15)
    Code:
    ATHLETE_PARTICIPATION
         AthleteParticipationID (autonumber)
         EventID (number???)
         AthleteID (text 15)
         T_F_ID (text 20)
         EventMark (number???)
    Code:
    EVENTS
         EventID (Autonumber)
         EventName (text 15)
         EventLocation (text 20)
         EventDate (date)
         EventSeason (text 10)
    Code:
    TRACK_AND_FIELD_EVENTS
         T_F_ID (text 20)
         EventName (text 15)

    The Athlete table is just that, it's the athletes themselves.

    The Athlete Participation is what events the athletes ran/jumped/threw etc... in the meet.

    The Event Table is something one of my teachers suggested I put in there, and that's the one I'm having troubles with. In the Event table, it's an autonumber but in the the athleteparticipation, it's a number.

    And the Track_and_Field table is all of the possible events in track and field. It is also describing the events.

    I guess, I was just wondering if I'm even going at this the right way and if anyone can help that'd be glorious.

    And if I didnt explain everything well enough just tell me what you want me to elaborate on and I will do that
    Last edited by Hyodoh; 02-19-12 at 21:50.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't understand the difference between (nor the need for) your two events tables

    the athlete participation table should ~not~ have an autonumber -- make the primary key a composite key consisting of athlete_id, event_id, and datetime
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,444
    Quote Originally Posted by r937 View Post
    i don't understand the difference between (nor the need for) your two events tables
    I believe that "TRACK_AND_FIELD_EVENTS" is a list of all of the possible sports that can occur at a given event and "EVENTS" are the names of the sporting events.

    e.g. the first table might contain values such as javelin, 100m sprint, long jump while the second table could contain 2012 Olympics, 2014 Commonwealth games.

    If this is the case then there is a M:M relationship between the two tables (many "events" can have many "track and field events") and as such would need an interim table to relate the two correctly instead. This would then have a knock-on effect to the participants table

    Boy that naming is confusing!
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    might be clear to give the tabels more meaningfull names
    eg
    meetings (hate the us term 'meet' seems such a butchering of the language) meetigns woudl be the name of the overall activity eg "xxx championship 2012"
    events (the name of the actual section eg 100m sprint, mens.

    Im guessing there may be another entity required eg: heats or legs to describe the various flights / qualifying rounds

    presumably there also needs to be something somewhere recording what a specific result was, whether thats elapsed time, or weight lifted or distance/height cleared. thats probably a candidate for sub / supertype
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2012
    Posts
    2
    hey guys thanks for responding, i really do appreciate, and i'm sorry for not getting back sooner, i've been pretty busy with like everything.. anyway..

    i don't understand the difference between (nor the need for) your two events tables

    the athlete participation table should ~not~ have an autonumber -- make the primary key a composite key consisting of athlete_id, event_id, and datetime
    Honestly, I really don't know why I have that either. I emailed my teacher and he was like, only 3 tables, that's clearly not enough.. Here use this it has more tables. Since I don't understand 1000% what's going on I just kind of went with him but it's still kind of meh...

    might be clear to give the tabels more meaningfull names
    eg
    meetings (hate the us term 'meet' seems such a butchering of the language) meetigns woudl be the name of the overall activity eg "xxx championship 2012"
    events (the name of the actual section eg 100m sprint, mens.

    Im guessing there may be another entity required eg: heats or legs to describe the various flights / qualifying rounds

    presumably there also needs to be something somewhere recording what a specific result was, whether thats elapsed time, or weight lifted or distance/height cleared. thats probably a candidate for sub / supertype
    I'll start working on renaming the tables, you're right in saying that is is kind of difficult to understand. Again I kind of went with what my teacher said I should go with for names.

    I believe that "TRACK_AND_FIELD_EVENTS" is a list of all of the possible sports that can occur at a given event and "EVENTS" are the names of the sporting events.

    e.g. the first table might contain values such as javelin, 100m sprint, long jump while the second table could contain 2012 Olympics, 2014 Commonwealth games.

    If this is the case then there is a M:M relationship between the two tables (many "events" can have many "track and field events") and as such would need an interim table to relate the two correctly instead. This would then have a knock-on effect to the participants table

    Boy that naming is confusing!
    Ha, you're right about the track and field events table, I'll try to clear up the naming somehow and repost what I get.

    In all reality though like my goal is to have something that one of our coaches can enter this info in a form on a website that gets put into the database and is shown on the website. I know I can do that part, I did something a bit more difficult for another class.

    But again thanks for the responses I'll try to work on this today and see if it makes more sense haha

    But if anyone of you have any suggestions on how to make it way simpler, I'll gladly take advice =p


    *EDIT*

    These auto numbers are kind of messing with my mind, specifically between the "MEETING" table and "ATHLETE_PARTICIPATION" table, is there a way to make this a little easier? I honestly don't fully understand why, but I would really be interested in learning why. I'll continue to do some research
    Attached Thumbnails Attached Thumbnails db.JPG  
    Last edited by Hyodoh; 02-29-12 at 13:11.

Posting Permissions

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