Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Unanswered: Table Structure Help

    Hi all,

    I'm s University student and I'm currently making a Swimming Record application and I'm struggling to decide upon the best structure for the tables in the database.

    The main bulk of the database consists of 3 simple tables:

    'Swimmers' - Which contains personal information on each swimmer.
    'Events' - Which stores data on swimming competitions.
    'Times' - Which records what times swimmers achieved in events.


    An example of an event is 'World Championships' and then each of the main events will have many different races that will include a combination of distances and swim strokes. It gets complicated because some of the events have pools that are measured in yards, which then means the distances are in yards so this means even more repeated data in the 'race_type' table.

    I've attached a picture of the table structure I settled with at first, but it doesn't seem quite right.

    An example of my data inserts for the 'event' table is show below:

    Code:
    INSERT INTO tbl_event(event_name, pool_length, event_date)
    (Select 'Sheffield Championships','50m', '2011-05-21')
    UNION
    (Select 'Yorkshire County Championship','50m', '2012-06-24')
    UNION
    (Select 'All Star Gold Championship','25m', '2010-08-7')

    An example of my data inserts for the 'racetype' table is shown below, the event_id column is blank because this is when i first started to feel that the table structure wasn't right:

    Code:
    INSERT INTO tbl_racetype(event_id, race_length, stroke)
    (Select '', '25m', 'Backstroke')
    UNION
    (Select '', '50m', 'Backstroke')
    UNION
    (Select '', '100m', 'Backstroke')
    UNION
    (Select '', '200m', 'Backstroke')
    UNION
    (Select '', '400m', 'BackStroke')
    UNION
    (Select '', '800m', 'Backtroke')
    UNION
    (Select '', '1500m', 'Backstroke')
    UNION
    (Select '', '25m', 'Breaststoke')
    UNION
    (Select '', '50m', 'Breaststroke')
    UNION
    (Select '', '100m', 'Breaststroke')
    UNION
    (Select '', '200m', 'Breaststroke')
    UNION
    (Select '', '400m', 'Breaststroke')
    UNION
    (Select '', '800m', 'Breaststroke')
    UNION
    (Select '', '1500m', 'Backstroke')
    UNION
    (Select '', '25m', 'Butterfly')
    UNION
    (Select '', '50m', 'Butterfly')
    UNION
    (Select '', '100m', 'Butterfly')
    UNION
    (Select '', '200m', 'Butterfly')
    UNION
    (Select '', '400m', 'Butterfly')
    UNION
    (Select '', '800m', 'Butterfly')
    UNION
    (Select '', '1500m', 'Butterfly')
    I'm just looking for a better way to structure the events part of my database, I'm not sure if I need more tables or whether I need to split the columns up differently or not.

    I hope what I've said makes sense, but if you need more information please ask. I hope you'll be able to guide me in the right direction.

    Regards,
    Attached Thumbnails Attached Thumbnails db.png  

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is an "event" a single race?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2012
    Posts
    4
    Quote Originally Posted by blindman View Post
    Is an "event" a single race?
    No, an event is a collection of races in this instance.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    sounds like you need a race table..or is called a heat?

    Then Event is a parent of race/heat...

    you should have a person table

    people can be swimmers, judges, timekeepers, lifegaurds, bartenders..whatever

    have Person be the Parent of Swimmers for example

    Have a SwimmersHeat table to record the times of each swimmer in a race

    have event be the parent of race/heat

    you need to collect ALL of the attrributes and see how they logically fit

    And look up Normal Forms to help you
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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