[SOLVED] Table organisation

[padams]

I'm designing a site for my school sports program and the situation I am struggling with is that students can play for different teams each year (for example they could progress from a B team to an A team), and can play for the same team for several years.

 

I need to be able to generate team lists and season statistics, and I can't work out how to organise my data so that I can select a team from a specific season, and have only those students who actually played for it returned from the query.

 

Has anyone got an idea how to organise the information?

[Mchl]

How about this?

 

table students
ID
name
surname

table teams
ID
sport
name

table student_team
studentID
teamID
season

[Barand]

can they change teams mid-season?

[padams]

Yes. It's not common but has been known to happen.

[Barand]

In that case, instead of season in that last table you may need date_from, date_to

[padams]

That shouldn't be a problem, as all we're after are summative statistics for each team in each season/term.

 

I've had a play with that table structure and it all seems to be working fine, and in fact it has helped me tidy up a few other things that were bugging me.

 

Very big help, thanks so much!