Soccer Referee Assignment

[matomo]

Hi all,

I am attempting to create a database that an area soccer referee assigner can use to assign referee to various soccer games.

The assigner has soccer referee, games and different teams. There are 3 soccer referee to each game, therefore the issue is that I need all the referees that have been assigned a game to be blocked out and only those that not been assign games should show in the form frmGameOfficials for a specific date.

I will appreciate if somebody can point me in the right direction on how to do this.

Thanks in advance.

matomo
View attachment TSSLGame Officials.mdb

 

[jdraw]

So you are saying that the referees are assigned from a list.
A referee is selected from the list and assigned to the game.
That referee is then marked (assigned/busy or something)
The selection then goes to the next referee who is Not Busy/assigned. Then s/he is marked busy/assigned. This continues until you get to the end of the list.

Do you allow referees to be unavailable for assignment for other reasons (sick, vacation, work, family, other)? This might remove that referee from a regular assignment, but make him available once his reason (sick...) has ended.

Do you have some sort of preferences for referees close to certain game locations? A referee will travel 5 miles but not 20 to referee a game?

Do you have other conditions along these lines that could get involved during referee assignments?

Do referees tend to work in groups or are they assigned individually and randomly?

Do all referees have to have an equal number of assignments over the season (as equal as referees and games allows)?

 

[matomo]

jdraw,

Thanks for the quick reply. All of the questions that you raised are true.

We allow referee to be unavailable for any reason

Referees do have some preferences for refereeing close to where they reside.

Referees can be assigned game individually or randomly.

We are stranding for referees to have equal number games at the end of the season.

I have attached a new database with the table named tblOfficials.

Thanks for anticipating what I am trying to achieve with this project.

matomo

 

[jdraw]

Another quick look suggests
Most fields in tblGameOfficials are not needed.

The purpose of your junction table, tblGameOfficials, is to relate Referees to Games. All you need is the PK from these 2 tables. You can get the related information via the relationships between the tables.
You do not , and you should not have, the fields in Games repeated in tblGameOfficials.

You set up does not identify game locations, which may limit how you can use some of the "soft constraints --- referee is 10 miles from game location"....

Similarly you do not allow for Referee preferences as I see things.

Do you have test data for validating your data model/table structure?

If you know what info you want to get out of the database, you have a good idea what must go in. If you have a referee with preferences, you would want those preferences to be part of the data and logic for making assignments.

Just some thoughts for consideration.

 

[jdraw]

Here is a model from Access World Forums dealing with soccer and games, but it doesn't deal with referees.

http://www.databasedev.co.uk/football_soccer_data_model.html

 

[matomo]

Thanks jdraw for pointing me to the model in Access World Forum. I will study this and let you know what I can come up with.

matomo

 

[jdraw]

I only mention it because I saw it previously and it does show some entities for games, locations and teams.

Good luck and let us know what you come up with.