padams Posted November 14, 2008 Share Posted November 14, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/132678-solved-table-organisation/ Share on other sites More sharing options...
Mchl Posted November 14, 2008 Share Posted November 14, 2008 How about this? table students ID name surname table teams ID sport name table student_team studentID teamID season Quote Link to comment https://forums.phpfreaks.com/topic/132678-solved-table-organisation/#findComment-690025 Share on other sites More sharing options...
Barand Posted November 14, 2008 Share Posted November 14, 2008 can they change teams mid-season? Quote Link to comment https://forums.phpfreaks.com/topic/132678-solved-table-organisation/#findComment-690396 Share on other sites More sharing options...
padams Posted November 14, 2008 Author Share Posted November 14, 2008 Yes. It's not common but has been known to happen. Quote Link to comment https://forums.phpfreaks.com/topic/132678-solved-table-organisation/#findComment-690435 Share on other sites More sharing options...
Barand Posted November 14, 2008 Share Posted November 14, 2008 In that case, instead of season in that last table you may need date_from, date_to Quote Link to comment https://forums.phpfreaks.com/topic/132678-solved-table-organisation/#findComment-690436 Share on other sites More sharing options...
padams Posted November 15, 2008 Author Share Posted November 15, 2008 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! Quote Link to comment https://forums.phpfreaks.com/topic/132678-solved-table-organisation/#findComment-690683 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.