I am trying to build a tournament scheduler application. In the database I have one specific problem. I have the following database columns:

• Hour (which is connected to the days that the tournament will run)
• Official (the person who will officiate the match)
• Field (the field on which the match will be played)

The problem I'm having is that if I 'connect' (use the PK of the above tables as a FK in my 'Match' table directly) I can have one official who can officiate two games at the same time OR I can have multiple officials who officiate on one and the same pitch at the same time OR I can have multiple matches on the same time on the same pitch.

How would I go about fixing this from a design point of view? I realise I could just limit the options one can choose programmatically but I'm really interested in how you can fix this from a design point of view.

I actually took the time to read this 3 times and still dont understand your question. Try not to abbreviate and act as if people you are asking have never heard of your problem (they havent... ).

Ok, my apologies. I see I typed "Database columns in the first line, that should be database tables".

Here's the problem breakdown:

I expect to have the following database tables: Tournament, Official, Day, Time, Location, Field, Team, Match and a couple of other ones which aren't important for my question.

If you think about the design of the 'Match' table you would probably have the following fields: TournamentID, Round, Date, Time, HomeTeam, AwayTeam, Result, Official, Field

Now say the application allows the tournament organisor to schedule the matches. He wants to plan the game between the NY Giants and the New England Patriots on the 12th of Februari at 6PM in Phoenix in the University of Phoenix Stadium officiated by John Doe. He would enter this via the UI and the record would be added to the Match database table.

Now say he wants to plan another match at the same time on the same day, he can't be allowed to pick the same stadium, and even if he picks another stadium, he can't pick the same official. I know how to prevent these problems from a development point of view, but I am interested to know how this kind of problem is handled from an analysis/design point of view. I.e. when drawing a domain model (problem model) or class diagram.

So you're asking, how can you set up your database to enforce the basic rules such as, there can only be one match going on at a stadium at a time, an official can't be in 2 separate matches at the same time, etc?

The standard way to go about this is to define all these such dependencies in your data and then use those dependencies to create the proper tables. There are some nice algorithms for this, do some searching up on "database normalization" and "normal forms" for more information.

Yours is going to be a little bit tricky with the interplay between 'Date/Time' and the officials/fields/etc.

I'm going to assume that you have a table where you combine those three fields in a record, that constitutes a scheduled match?

You can make some unique keys of hour/official and hour/field. That prevents you from double-booking either resource.

That said, I'm not sure that's a better solution than decent business rules that prevent it from happening.

One way to do this would be to setup a stored proc that is used to create your Match records. It would contain the business rules that would prevent assigning an official to duplicate matches at the same time or to different facilites. Your code would then receive a status code that signifies either the record was succesfully created, or that an error occurred (the status code should designate why the error occurred).

Isn't it considered bad practice to code business rules in stored procedures? If you do this you'll split your business logic in two places, in the code and in the stored procedures. I'm not 100% sure about this tho

These are business rules. If they get too complex to model in the database structure, just let the application code or ORM take care of enforcing them.

Okay, many thanks for the replies. I'll use business rules to enforce these constraints.

Generally speaking yes. However, this rule relates only to the creation of the record and could be considered immutable (it will never be the case that an official could suddenly be able to referee multiple matches at the same time). But for the most part business rules should be contained in the code