Results 1 to 7 of 7
  1. #1
    flebber is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2009
    Location
    Australia
    Posts
    9

    Database Relations between Names and Events

    Hoping you could help. I can't get a concept in my head of how I should design my database structure.

    Currently I have set too tables TrackName and CompetitorName.

    Track defines TrackName, State, Location, Category, Track Conditions.
    Competitor defines Name, Location, Trainer.

    What I am not sure how to include is starts/distances. Each track has a number of starts. Each competitor can compete at many tracks and distances. Most of the distances are not unique to the track, but some are.

    Each distance to a competitor will be broken up into sections distances and a time recorded for each. This is how it would look in excel as it may make it clearer(hopefully).



    Code:
    TrackName 	Starts
    Rosehill	1600m
    Rosehill	1400m
    Rosehill	1200m
    Rosehill	1000m
    Randwick	1600m
    Randwick	1500m
    Randwick	1400m
    Randwick	1200m
    Randwick	1100m
    Doomben	1600m
    Doomben	1500m
    Doomben	1350m
    Doomben	1200m
    Doomben	1100m
    Code:
    Date	30/06/2011						
    Location	Rosehill						
    Event number	1						
    Distance	1200m						
    Competitors	1200 - 1000	1000- 800	800 - 600	600 - 400	400 - 200	200 - 0	Total(Sum)
    Competitor1	12.12	12.22	12.51	11.9	11.76	11.71	72.22
    Competitor2	12.22	12.14	12.32	11.81	12.01	11.56	72.06
    Competitor3	12.14	12.51	12.17	11.76	12.11	11.95	72.64
    Competitor4	12.51	12.32	12.13	12.01	11.91	11.77	72.65
    Competitor5	12.32	12.17	12.21	12.11	11.84	11.55	72.2
    Competitor6	12.17	12.13	12.11	11.91	11.92	11.97	72.21
    Any advice appreciated

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,623
    Review the 'sticky' thread tutorials here http://forums.aspfree.com/microsoft-access-help-18/

    Identify data entities and design schema. Post your design for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    flebber is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2009
    Location
    Australia
    Posts
    9
    Code:
    Model	To record results of competitors at events					
    Entities	Competitors					
    	Tracks					
    	Trainers					
    	Events					
    Relationships	Trainer can have many competitors					
    	Tracks can have many events					
    	On the same day a competitor can not be in more than one event					
    	Events can have many competitors					
    	Tracks can have many trainers and competitors
    Code:
    Table Competitors	Tracks	Trainers	Events
    CompName_ID	TrackName_ID	Trainer_ID	Event_ID
    Name	State	First Name	Date
    Mother	City	Last Name	Time
    Father	TrackName		Location
    Age	Location Category		Event Number
    Height	Distances(Starts)		Competitor Number (Per Event)
    	Distances(Sectionals)		Times(Race Total)
    	Track Condition		Times(Sectionals)
    			Times (Race Per Competitor)
    			Times (Sectionals Per Competitor)
    			Competitor Weight
    There were a further two categories I couldn't fit in as they were more descriptive and not specific to any particular table.


    Location (Home Track) Competitor
    Location (Home Track) Trainer

    My other concern is that whilst some distances are unique to some tracks most are not. I didn't want to continually replicate distances but wasn't sure if distance was its own entity either.

    Edit: This may be better, Or not. I have used FK to mean foreign key.
    Code:
    Table Competitors	Tracks	Trainers	Events	Distances	Distance Sectionals	Times
    CompName_ID	TrackName_ID	Trainer_ID	Event_ID	Distance_ID	Sectional_ID	Times_ID
    Name	State	First Name	Date	Distance_Starts	Dist_Sectionals	TimesSectional
    Mother	City	Last Name	Time	Track_FK	Dist_Starts_FK	Sectional_FK
    Father	TrackName		Location			
    Age	Location Category		Event Number			
    Height	Distances(Starts)		Competitor Number (Per Event)			
    Trainer_FK	Distances(Sectionals)		Competitor Weight			
    	Track Condition		Trainer_FK			
    			Competitor_FK			
    			Track_FK

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,623
    Good start, but you might need some junction tables. For instance, a table for Events and table of Competitors. Events can have many competitors and competitors can be in many events, a many-to-many relationship. Junction table will associate an event with competitor:

    Events
    EventID
    Location
    EventDateTime
    EventNumber (why needed?)
    Track_FK

    CompetitorEvents
    EventID
    CompetitorID
    CompetitorNumber
    CompetitorWeight
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    flebber is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2009
    Location
    Australia
    Posts
    9
    How do I define a 'Junction' Table as different from a normal table?

  6. #6
    flebber is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2009
    Location
    Australia
    Posts
    9
    oh found a good answer here on yahoo answers for junction tables.http://answers.yahoo.com/question/in...2204714AAanTBr

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,623
    A junction table is just a table that serves the special purpose of associating the records of two many-to-many related tables.

    That was a good reference.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. File Names in "Open Recent Database" list
    By Ron.Sul in forum Access
    Replies: 3
    Last Post: 12-29-2011, 10:06 AM
  2. Problem with relations
    By PoorCadaver in forum Access
    Replies: 17
    Last Post: 10-18-2011, 12:31 PM
  3. Query Entity Names, and Column Names
    By Accidental DBA in forum Queries
    Replies: 1
    Last Post: 07-22-2011, 02:38 PM
  4. Replies: 5
    Last Post: 04-24-2011, 03:14 AM
  5. one to one relations
    By crackpot in forum Database Design
    Replies: 2
    Last Post: 08-18-2010, 09:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums