SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Advice on the design
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

marric01
Starting Member

Canada
2 Posts

Posted - 07/29/2013 :  10:23:18  Show Profile  Reply with Quote
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 advice

marric

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 07/29/2013 :  10:48:09  Show Profile  Reply with Quote
I would do this slightly differently. I would have the following tables:

1. Schedule
2. 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.

Edited by - James K on 07/29/2013 10:49:06
Go to Top of Page

marric01
Starting Member

Canada
2 Posts

Posted - 07/29/2013 :  13:14:45  Show Profile  Reply with Quote
Hi James

Thank 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 day

marric
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000