Database Design – Enforcing One-to-Zero-or-One Relationship with Multiple Types

database-designsubtypes

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:

Visualization

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?

Best Answer

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.

Related Question