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 ?
3
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.
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.