4

We have two systems, System A imports a list of sports events from system B, it only wants to import the events we will be providing live data for, the filter used to hide events that we are not providing data for is if the event has a referee assigned to it.

The problem with this is that we don't know who the referee will be for particular events but we know there will be one so someone has created a referee with the name TBC and assigned it to a bunch of events so they will get imported.

Now there is a request to be able to mass assign this TBC referee to multiple events to save our operations staff from having to manually assign the referee for each event one by one.

This to me seems like a very bad idea, I don't like the idea of a referee called TBC in the first place but i can't really explain why, just seems like an anti pattern to me.

Can someone help me out and explain if it is a bad idea or not and why ?

closed as primarily opinion-based by GlenH7, user40980, Kilian Foth, Jarrod Roberson, gnat Feb 24 '15 at 16:51

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    It does have the virtue of avoiding the use of NULL. Then again, you could always have NULL mean "unassigned." – Robert Harvey Jan 14 '15 at 17:12
  • In this case, though, is there really a virtue in avoiding NULL? This is exactly the situation NULL exists for. There will be a referee for the game, but it is currently unknown (therefore, NULL). – Locke Jan 14 '15 at 18:44
  • Does system B use NULL referees to indicate no ref assigned? If so then the suggestions already posted don't work with the database as designed – StarWeaver Jan 14 '15 at 20:16
2

Let us assume there's an EVENT table and a REFEREE table, and that the first one has a PK (referee_id) which refers to REFEREE's PK (referee_id).

Then one "TBC" referee (not a real person), is inserted in table REFEREE, so any event that has no referee assigned a the moment of insertion can at least be assigned the code of that imaginary referee.

That doesn't look like a very good idea to me, compared to simply leaving the FK in it's null state:

  • As FKs must accept null, there's always the possibility of having events with "TBC" as well as others with nothing (null). That means code must handle both situations everywhere. For example a report listing all events with no referees should consider both cases.

My recomendation is:

Create a view that shows the literal "TBC" whenever there's no assigned referee, like this:

create view v_events as
select
    e.event_name,
    e.event_date,
    nvl(r.referee_name,'TBC') as referee_name
from
    event e left join referee r (on e.referee_id = r.referee_id);

In the case the referee is not it's own table but just a text column in EVENT:

create view v_events as
select
    e.event_name,
    e.event_date,
    nvl(e.referee_name,'TBC') as referee_name
from
    event e;

Note: NVL is an Oracle function, substitute with an equivalent function in your RDBMS, like COALESCE in Postgres.

Have all code select that view instead of the real table. That way everyone will think there's a "TBC" referee assined by default.

-1

There seems to be too many business rules that rely on the existence of a referee id: Who is the ref and whethere or not this event is streamed. For all the streamed/non-refereed events, the field is null. It would be difficult to look at your database and make this determination.

I don't know how much of your data structure you could change, but this holding ID for a TBD referee is going to have to be addressed in other queries as well.

Ideally, there should be some sort of table:

EventReferee

  • EventID
  • RefereeID

    • a lot of other data can be put here that may typically also be null in your Event table: assignment date, etc.

This way, you would have a record in this table to designate those that do not provide a live feed/have a referee, but still may or may not have a referee ID assigned.

A query for who still needs a referee assigned is very simple by checking for null.
A query for events that have a referee will have this record related to the event. A query for events that a streamed will not have a record in the EventReferee table.

Not the answer you're looking for? Browse other questions tagged or ask your own question.