Sports Teams Database

Hi Guys,

I have an existing database to capture the sport played by a pupil in their school.

I have a table called tblSports:

ID Decr
1 Cricket
2 Hockey

I have a table called tblTeams
ID Descr
1 Under 14A
2 Under 16

Since Cricket and Hockey can have an Under 14A Team I have a table called Sport_Teams which links the two.

ID SportID TeamID
1 1 1
2 2 1

Now I have a web page where a user ticks the team a pupil has represented. That gets save in a table called peoplesport:

ID SportID TeamID PupilID Year isTicked EventDetailID

I added the eventdetailID so I can build a team list per event.

Now the need has come up to create an Event (EventID, EventDate, Venue, Description)

and link an Activity(Sport or Extramural Activity) to the event. Extra murals don't have a team.

So after creating an event, you then select the Activity Type. If it is a sport you the select your sport(soccer, cricket), then your Team.

So i have the Event table as listed above. I created an EventDetail table to store the EventID, ActivityType and ActivityID.

If it is a sport I save the activitytype as 1 and insert records into the tblSport table.

If it is an extramural I save the type as 2 and insert into another table.

My question is, is this structure wrong? How can I design this to work well. I want to expand this to save statistics per sport etc.

My basic need is create an event. Select the activity type. Select the sport, the team and display a list of pupils that fulfil a certain criteria(grade, age group) etc in a listbox

Then I have another listbox where I drag a pupil onto. This will be my team list. Then I save the team for this specific event