Primary keys due to inheritance

Question

I am trying to design a database for postgrad office and we have a parent class "User" that has a username as a primary key for authentication and we have corresponding subclasses "Student" and "Supervisor" and each of them has an ID as a primary key.

Should the primary key for the corresponding subclasses be a composite primary key or how to handle such case?

Answer 1

In these cases, I've always used the same PK value between Main Class and Subclass. This helps ensure a 1:1 relationship.

Create table Users (
  User_id int primary key, -- auto increment
  Username varchar(50) not null unique
);

Create table students (
  User_id int primary key references Users(User_id)
);

Create table Supervisors (
  User_id int primary key references Users(User_id)
);

This also assumes that a Student can be a ~Master~ Supervisor.

(Not sure if code is correct)

Answer 2

As mentioned by @MichaelKatz, commonly this is implemented with the primary key as a foreign key. In other words, it is a 1:0-1 relationship.

But an interesting twist is to add a Type column, this identifies what type the base User row is so you know which rows to join onto.

This becomes part of a composite primary key. The same key is used on the sub-tables, only the Type column is a fixed computed column

Create table Users (
  User_id int identity,
  Type tinyint,
  Username varchar(50) not null,
  primary key (User_id, Type)
);

Create table students (
  User_id int,
  Type AS 1,
  primary key (User_id, Type),
  foreign key (User_id, Type) references Users (User_id, Type)
);

Create table Supervisors (
  User_id int,
  Type AS 2,
  primary key (User_id, Type),
  foreign key (User_id, Type) references Users (User_id, Type)
);