Database Relations between Names and Events

[flebber]

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).

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

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

[June7]

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.

[flebber]

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

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.

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

[June7]

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

[flebber]

How do I define a 'Junction' Table as different from a normal table?

[flebber]

oh found a good answer here on yahoo answers for junction tables.http://answers.yahoo.com/question/in...2204714AAanTBr

[June7]

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.