Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marric01
Starting Member
2 Posts |
 Posted - 2013-07-29 : 10:23:18
|
| Hi,I need an advice on how to model this scenario.I have created a set table for hockey league manager.Schedule (ScheduleID,Date,Time,DivisionFK, etc..)ScoreKeeper (ScoreKeeperID, name,email,phone)Referee (RefereeID,name, email, phone, comments,active)In a specific game, theres always only 1 scorekeeper and theres always 1 to N referees.How should I model this ? Can I put the ScoreKeeperID in the Schedule table as a FK ?Thanks for any advicemarric |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-29 : 10:48:09
|
| I would do this slightly differently. I would have the following tables:1. Schedule2. Persons (this will store info on human beings regardless of their role - scorekeeper, referee etc.)3. Referees (this would have at least two columns - ScheduleId and PersonId. May have other columns to indicate other info, for example, roles like line judge or umpire).4. ScoreKeepers (this is a judgement call. You could include the scorekeeper column in Schedule table, but in case your requirements expand to have multiple scorekeepers, perhaps a backup scorekeeper in the event that the main guy or gal came down with a severe case of flu etc.)Depending on your requirements you might have other tables - for example, another table might be a table with Roles - ScoreKeeper, Referee, Player, Benchwarmers etc. Yet another table might be a link table between People and Roles, i.e., something which lists what each person is qualified for. Someone may be a scorekeeper most of the time, but they may be qualified to serve as a referee in a pinch.Just some thoughts. Feel free to revise/shoot it down/comment as you see fit. |
 |
|
|
marric01
Starting Member
2 Posts |
Posted - 2013-07-29 : 13:14:45
|
| Hi JamesThank you for the precious advices. I think that i'm gonna go with the Persons table with roles. It's gonna be easier for future modifications to the system.Thanks again for the fast reply !Have a great daymarric |
|
|
|
|
|
|
|
|
Advice on the design