How can I enforce a one-to-zero-or-one relationship with multiple types? 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?
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 &lt;name&gt; 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 &lt;name&gt; 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 &lt;name&gt; CHECK (MEMBER_TYPE = 'R');
ALTER TABLE REFEREE ADD CONSTRAINT &lt;name&gt; 
     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.