Questions tagged [discriminator]

Inheritance hierarchies arise naturally in object-oriented design and programming; however when it comes to persisting the object a relational database management system (RDBMS) is normally used. A discriminator is a column used to describe inheritance. It is often, but not exclusively, associated with the single table per class hierarchy.

Consider the following example that consists of students and teachers. One can create a people table that contains the common information about the students, say first and last name. The categories table will contain two entries, student and teacher and the column fk_category on the people table functions as a discriminator.

CREATE TABLE categories
(
  id serial NOT NULL,
  category character varying(128) NOT NULL,
  CONSTRAINT people_pk PRIMARY KEY (id)
)


CREATE TABLE people
(
  id bigserial NOT NULL,
  fk_category INT NOT NULL,
  first_name character varying(128) NOT NULL,
  last_name character varying(128) NOT NULL,

  -- other columns common to teachers and students

  CONSTRAINT people_pk PRIMARY KEY (id),
  CONSTRAINT people_fk FOREIGN KEY (fk_category)
  REFERENCES categories (id) MATCH SIMPLE
)


CREATE TABLE students
(
  fk_person bigint NOT NULL,

   -- other columns specific to students

  CONSTRAINT students_pk PRIMARY KEY (fk_person),
  CONSTRAINT students_fk FOREIGN KEY (fk_person)
  REFERENCES people (id) MATCH SIMPLE
)

CREATE TABLE teachers
(
  fk_person bigint NOT NULL,

   -- other columns specific to teachers

  CONSTRAINT teachers_pk PRIMARY KEY (fk_person),
  CONSTRAINT teachers_fk FOREIGN KEY (fk_person)
  REFERENCES people (id) MATCH SIMPLE
)

More Information:

1. Wikipedia on Object-relational mapping
2. The first chapters of Java Persistence with Hibernate provide an excellent overview of the problem and potential solutions.