Sport Teams Database with age restrictions

Hi Guys,

I have a table called Sport (ID, Description, Active)

Example data:

Code:

---

1  Soccer  1
2  Cricket  1

---

Then I have a Teams table(ID, Description, Active)

Code:

---

1    Under 14A  1
2    Under 15A  1
3    Under 15B  1

---

Since soccer and cricket can have an Under 14A team I have a table called Sport_Teams that links a team to a sport

ID, SportID, TeamID

Code:

---

1    1          1
2    2          1

---

Those are the basic tables. I need to add an age restriction to a team. I have a people table(Peopleid, Name, Surname, DateOfBirth)

When I select a team from a dropdown, I need to then only display the people who's age group falls under the age restriction for the selected team. What is the best way to do this. for example if I select Soccer, then the Under 14A team, I then need to only list people who are under the age of 14.

Please advise me. Thanks

If you need to display people of certain age when "age group" is selected you MAY define "age_group" table and associate it with "peoples" table by adding a foreign key to peoples table - (below is a sample relationship between two tables):

age_group table:
Age_Group_ID
Description

Peoples table
[all fields that you need]
Age_Group_ID (FK - one to many)

I think I'd come at this from a slightly different direction from Rhino but only because I'm reading the problem slightly differently.

It doesn't sound to me like you need an Age Group as such, you just need to know what the age range for each team is. And since there all called UnderX I suspect you actually just have a max age for each team.

With that in mind I'd add a MaxAge column to Teams. When a team is selected you can then select the apropriate people records something like this:-
Select Name, Surname
From People
Where DateAdd(Year, DateOfBirth, @MaxAge) < GetDate()

(You would pass Max Age in as a parameter when they slected the team)

The main difference between mine and RB's aproach is that mine is more "dynamic" which may be a good or a bad thing. RB's aproach requires you to run a regular update to move people from one Age Group to another as they age, mine doesn't. That means that mine will naturally return you people who are Under 15 right now. If that's what you want then I'd suggest it's the better aproach becuase it requires no maintenance.

But with things like this a persons eligibility is often based on their age at the beginning of the year rather than their age right now. If that's the case then things aren't so clear. My aproach would require quite a complicated join whereas Rhino's simply requires an automatic update to run once a year to move people into the right age group for the coming year.

Which is right really depends on your exact requirements.