Re: Practical - Design - With Dirty Hands

> OP: use natural keys where possible, and if it's an SQL database make
> sure the foreign keys are declared ON UPDATE CASCADE.  The guidelines
> for choosing keys are: uniqueness, stability, familiarity and
> simplicity.  (And stability does not mean immutability.)
>
> --
> Roy

So thats sort of interesting regarding stability. Most of my model is fairly sound but it's in managing players teams, coaches and games in my model that has caused me the most concern in understanding and application.

Essentially while the person(player) may be stable his role position and team are not. So a player should be signed to a team for a set period of time(season) however this is not guaranteed(may transfer), the players position might change or might not play at all. Because of this I created the players table listing only characteristics of the person(first name, last name, age, weight, height), I then created a team table. To manage the players to team I created a junction table of players and teams which included a start and end date so a player was only bound to a club until I supply an end date.

However because of this the PlayersTeams junction tables foreign key was no longer specific to just the player but also the team, Therefore when using the ID field from PlayersTeams in games to access in GamesTable it needs to access the team name(for homeTeam & awayTeam, and the teamCpatain home & away. In the PlayersTeams table the team and players fields are only INT fields that reference the ID's from their respective team and players tables.

Being new this is where I started to have trouble and definitely looking for more info about design.

Sayth Received on Fri Apr 27 2012 - 14:04:39 CEST