0

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. EERD Diagram

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

2

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)

0

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)
);
1
  • And with the Type column (aka a Descriminator) you can also model the inheritance hierarchy as a single table with nullable columns for subtype-specific attributes. Nov 6 at 22:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.