Re: Practical - Design - With Dirty Hands

flebber wrote:

>> 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.)

> 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.

Health warning: this is Usenet. It is peopled with a diverse collection of real experts, charlatans, self-proclaimed experts, experienced professionals, tireless oafs, smart-alecs, ivory tower academics, zanies and trolls.

You must make you own decision about what I am.

Ordinarily I wouldn't try to help anyone with a real design problem but as this is plainly an exercise I'll give my more (or less) valuable opinion for free.

Exercises are sometimes harder than real world projects because they are chosen especially to focus on intrinsically difficult problems that are a small part of database design in the real world. Also you know this problem is artificial so you will be constantly second-guessing your solution.

Having said that, here are some comments:

Focus on the stated problem (the nearest you are going to get to the part of the business that is of interest). Ask yourself what statements you can make about each part, and what questions you need to answer.

Do NOT engage in conjectures about unstated problems and complications. That is a very important discipline and if you can master yourself to stick to only what you were told to focus on, it will make your job easier. It is very tempting to bring your own knowledge to bear, and to speculate about complications, but resist it vigorously. (In the real world you would of course check with the customer to make sure they've not overlooked something.)

So, knowing only what you've said about the problem, I suggest that the team table is easy. It's identity is unambiguous, so you can use natural keys (such as its name--although in another frame of reference its name is actually a synthetic key!).

You can use its name as a foreign key in your other tables too (but remember ON UPDATE CASCADE). It *could* change, but not ofen, so it's stable. It's certainly familiar. It is required to be unique. So it's a good key.

Players are way more tricky. People always are. Depending on the business process you may be justified introducing a synthetic key for the players. You would of course have to have some business process to ensure that you don't accidentally create a row for the same player twice, with a different synthetic key each time, but that's not a database design problem.

But as this is a database that is intrinsically dealing with small numbers of people, you *might* be perfectly justified to argue that in THIS enterprise of interest no two players will have the same name AND the same home phone number. Yes--you can propose all kinds of more or less far-fetched ways that assumption is not guaranteed to he true for all time time in the universe, but within your narrow focus it might be acceptable.

If it is not--for reasons that *actually* obtain (as opposed to hypothetical reasons that are not known to actually obtain but are merely conceivable) then you would be justified in introducing a synthetic key, subject to the business process I mentioned above.

HTH

-- 
Roy