key Integrity

[musicarteca]

I have two tables, Teams and Players. The teams table has a list of names that repeat for each division (age group). I had two approaches for the key field:

The first one would be to have an auto-enter serial number on the teams table (team_ID); on the players table the key would be selected displaying a long value list that shows a concatenated field of division and team name, but enters the team ID (serial number). The only thing that I don't like on this approach is the long VL.

The second approach would be to use the two fields as keys (division and team name), and enter them in two value lists at the players table. With this approach although simpler, I am afraid that any division / team coding or names might change in the future, disrupting the whole relationship. What is your experience or advise on this matter?

[T-Square]

I strongly urge you NOT to go with the second approach; it will complicate things immensely and make the maintenance more difficult.

Moreover, presumably a "Team" would constitute a group of "Players" who play simultaneously at given locations and times. Inherent in this "Team" concept is the Age group to which it applies. So, you shouldn't need two fields for division and team, since each team by definition includes the division. The Team_ID will suffice.

Keep the team_ID as you describe it, and use the value list--honestly, how many teams are you talking about? 30? 50? 500? You could use calculated fields in the value list that would include both team and division, making it clear which team is being selected--heck, you can even put the elements in the ideal order for your needs (team/div or div/team) to make it even clearer.

Another way of handling the input would be to select the players for a team, rather than a team for the player. In this case, you'd add a portal to the Team layout linking to the Players table, which would show team members and allow adding new members as needed.

HTH,

David

[musicarteca]

Thank you very much David, I am doing exactly what you suggested, I also thought of the possibility of building the team in a portal at the team's table, but wanted to avoid all the possibilities that the same player is chosen to more than one team at the same time.

[T-Square]

If you've set up your player table so that there is a single field in the player record that stores the team, then you shouldn't run into the problem of selecting a player for more than one team. Once you selected a player in the Player portal, the Team_ID field in the player record would get changed.

This raises the point that such a design would encourage you to accidentally reassign players to the current team, which is not good. You'd be better in this circumstance to have the portal display team members, but not allow you to change the assignments or add team members.

Moreover, th next question I would ask is whether a single player might play for more than one team. If so, you'd need to re-structure your database to use a "join file" to allow a many-to-many relationship. This join file (Let's call it Team_Members) would at the barest have a Team_ID (linked to the Teams table) and a Player_ID (linked to the Players table). It might also have start and end dates (a team member plays on the team for a given period of time), and perhaps other aspects of team members.

Heading down this route allows greater flexibility, but at the cost of much more complexity. Your routines, for example, would have to check that a single player isn't assigned more than once to a given team.

[musicarteca]

Thank you David, It is a one to many relationship, since each player can play in one team only (at least on this club), and I have it set up just as you described it:

On the players table I have a field to choose the team and to the left of the layout there is a read-only portal that displays all the teammates for that team. That pretty much covers the possibility of choosing the same player to more than one team.

When you talked about dates, you raised an interesting point, that I described in another post, but had no answer. This db will work only for the present season, so the players of this year will be the default for next year's teams and edited accordingly.

So I had the question on what is the common procedure to archive the information from a season, whether by exporting to another table or an excel sheet at the end of the season or another automated procedure. What are your thoughts?

[T-Square]

I'd just save the whole database file with the year added to keep the archive clear. And I'd store it as a compressed file (to ensure that FM doesn't start editing the older copy).

[musicarteca]

That is a good idea, I was thinking of storing the data in Excel or PDF, but I think it would be better and easier saving a duplicate and adding the year. Thanks.