How can I enforce a one-to-zero-or-one relationship with multiple types?

Question

I am creating a database structure to represent a football league, but there is a certain part that I do not know how to solve, that is:

• A member and he can be a referee, a management or a player (but only one of these types).

I include the following drawing to exemplify the situation:

The attributes for each type are:

• member: id (primary key), name, surname, etc.

• referee: id (primary key), practice, league (foreign key to league)

• management: id (primary key), practice, club_id (foreign key to club)

• player: id (primary key), number, club_id (foreign key to club)

Question

With this scheme, a member can be a referee and a player at the same time, but I want to enforce a rule that dictates that a member can be only a refeere or a player, so how can I achieve this goal?

Answer 1

You can add an attribute to MEMBER that defines the type. Example:

CREATE TABLE MEMBER
( MEMBER_ID INT NOT NULL PRIMARY KEY -- ID is to vague IMO
, MEMBER_TYPE CHAR(1) NOT NULL
, ...
, CONSTRAINT <name> CHECK (MEMBER_TYPE IN ('R','M','P'))
) ;

I've heard that some DBMS support CHECK CONSTRAINTS with SELECT and for such you can add a CHECK constraint in each sub table that verifies the type

For DBMS that does not support that you can add a unique constraint in MEMBER:

ALTER TABLE MEMBER ADD CONSTRAINT <name> UNIQUE (MEMBER_ID, MEMBER_TYPE);

and "inherit" the MEMBER_TYPE attribute to the sub tables:

ALTER TABLE REFEREE ADD COLUMN MEMBER_TYPE CHAR(1) DEFAULT 'R' NOT NULL;
ALTER TABLE REFEREE ADD CONSTRAINT <name> CHECK (MEMBER_TYPE = 'R');
ALTER TABLE REFEREE ADD CONSTRAINT <name> 
     FOREIGN KEY (MEMBER_ID, MEMBER_TYPE)
     REFERENCES MEMBER (MEMBER_ID, MEMBER_TYPE);

Another idea is to add validation triggers to the sub tables that validate that the member is of correct type before inserting or updating.

Answer 2

I assume that the 'business logic' behind this is the conflict of interest where a player (or manager) who is also a referee could potentially help his team cheat. If you truly want each member to only be one type, then you can have an attribute 'memberType' in the member table, which would hold a value of 'R', 'M', or 'P' (you get the idea).

Sooner or later, you'll want 'StartDate' and 'EndDate' for each of the three 'type' tables (consider when a player becomes a ref or a manager), and perhaps even audit tables, depending on how much movement there is between roles (and clubs) over time. But let's start by answering your question: add 'memberType' to your member table. Your application and/or a check constraint can enforce the business logic.

Answer 3

There is another different possibility, for DBs that do not accept CHECK clauses with SELECT in it: add BEFORE INSERT or UPDATE triggers to your "referee", "management" and "player" tables. The triggers should check for the NON-existence of another entry on the other two tables.

This would be an example (I use PostgreSQL). This is your basic schema, as you defined it:

CREATE schema football ;

CREATE TABLE football.member
(
    member_id integer PRIMARY KEY,
    name text,
    surname text
) ;

CREATE TABLE football.referee
(
    member_id integer PRIMARY KEY REFERENCES football.member(member_id),
    practice text,
    league text
) ;

CREATE TABLE football.management
(
    member_id integer PRIMARY KEY REFERENCES football.member(member_id),
    practice text,
    club_id integer /* REFERENCES club(club_id) */
) ;

CREATE TABLE football.player
(
    member_id integer PRIMARY KEY REFERENCES football.member(member_id),
    number integer,
    club_id integer /* REFERENCES club(club_id) */
) ;

Without changing this, you can create three trigger functions, one for each of your "referee", "management" and "player" tables:

CREATE OR REPLACE FUNCTION football.check_referee_only() RETURNS trigger AS
$BODY$
BEGIN
    -- Check that referee is not a manager
    if EXISTS (SELECT * FROM football.management x 
                WHERE x.member_id = new.member_id) THEN
        RAISE EXCEPTION 'Member % is already a Manager. Cannot also be a Referee', new.member_id;
    END IF;

    -- Check that referee is not a player
    if EXISTS (SELECT * FROM football.player     x 
                WHERE x.member_id = new.member_id) THEN
        RAISE EXCEPTION 'Member % is already a Player. Cannot also be a Referee', new.member_id;
    END IF;

    -- Nothing wrong... just return
    RETURN new ;
END ;$BODY$
LANGUAGE plpgsql VOLATILE ;

-- You should also define equivalent two functions 
-- for the other two roles (player and manager) 

Now you create three triggers that should fire when an INSERT or UPDATE is about to happen in each of your tables on the right hand side:

CREATE TRIGGER trg_check_referee_only 
   BEFORE INSERT OR UPDATE OF member_id
   ON football.referee FOR EACH ROW
   EXECUTE PROCEDURE football.check_referee_only();

-- You should also define equivalent two triggers 
-- for the other two roles (player and manager) 

And now we check that it works:

-- We create one member
INSERT INTO football.member(member_id, name, surname) VALUES (1, 'John', 'Doe') ;

-- We make it a player...
INSERT INTO football.player(member_id, number, club_id) VALUES (1, 12, 12345) ;

-- If we try to make it a referee as well => an exception will occur
INSERT INTO football.referee(member_id, practice, league) VALUES (1, 'some practice', 'some league') ;

When executing the last statment, you would get the following error:

ERROR: Member 1 is already a Player. Cannot also be a Referee
SQL state: P0001
Context: PL/pgSQL function football.check_referee_only() line 10 at RAISE

I would probably concur that this is not your best solution if you're now designing your data representation from scratch. I would probably use also a "member_type" column instead.

However. if the data structure is already in place, it is not practical to change it (because you would have to change a lot of code, for instance) and you still need to guarantee your rules, this would be a way to do it. It could also be extended later on to take into account other history conditions (as pointed out by @Cliff), and not check for only x.member_id = new.member_id, but take into account start_date and end_date, or other conditions.

This approach also has another advantage: you could have, later on, a fourth table (let's call it "fan"), and you would like to forbid a "fan" from being a "referee", but not from being a "manager". This time, a column "member_type" wouldn't be a good representation of the fact that someone is a "fan" and a "manager". In any case, you could design your triggers to guarantee any combination you consider suitable.

Main disadvantage: triggers have a certain runtime penalty, you have to test them carefully, and they're heavily dependent on your particular database (what is good for oracle might not good for sql server, for instance).