User table for many tables to login

Question

I'm trying to build school management system and I'm having trouble designing an optimal database structure. I have Students, Staff and Users tables for login. User table will have login information only (userNumber, password) and Students and Staff will contain personal information. I separated Students and Staff because they contain different personal data. But they both have a userNumber.

users(
id,
userNumber,
password
)

students(
id,
studentNumber,
name,
age
)

staff(
id,
staffNumber,
name,
age,
salary,
dateOfHiring,
staffType
)

Let's say I'm login in with a userNumber 98242, how can let the system know where should I look, in Students table or Staff table?

I would like some recommendations on database structures.

Answer 1

just add column userType to users table

Answer 2

You could do a few things. You could create a type in the users table and look that up. You could also join the both the tables and then on recieving a record check if the student id or staff id has been returned.

Then your query could be something like

SELECT users.id as user_id, students.id, staff.id FROM users 
LEFT JOIN students ON users.id = students.id
LEFT JOIN staff ON users.id = staff.id
WHERE id = 98242

Answer 3

Inheritance:

create table persons (
  id,
  name,
  age
);

create table users (
  number,
  password
) inherits (persons);

create table students (
) inherits (users);

create table stuff (
  salary,
  dateOfHiring,
  staffType
) inherits (users);

Schematically, something like this. Using tableoid system column you could to know the origin of the particular row:

select
  *,
  tableoid::regclass -- Prints the origin table name (users, students, stuff, ...)
from users
where number = 98242;

Answer 4

While there are only few separate columns for students and staff members, I would keep it simple:

CREATE TABLE person (
  person_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, name      text
, birthday  date  -- never age! bitrots in no time
, student_number int
, staff_number int
, salary       numeric
, hired_at     date
, staff_type   text
, CONSTRAINT one_role_max CHECK (student_number IS NULL
                              OR (staff_number, salary, hired_at, staff_type) IS NULL)
, CONSTRAINT one_role_min CHECK (student_number IS NOT NULL
                              OR (staff_number, salary, hired_at, staff_type) IS NOT NULL)
);

CREATE TABLE users (
  user_number int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, person_id   int NOT NULL REFERENCES person
, password    text -- encrypted !
);

This way, one person can have 0-n user accounts - which is the typical reality. You can restrict to a single account per person by adding UNIQUE (person_id) to table users.

The CHECK constraint one_role_max enforces that either student columns or staff columns must stay NULL.
The CHECK constraint one_role_min enforces that at least one of both must have any values.
Adapt what must/can be filled in to your needs. The expressions work excellently for the current design. See:

• NOT NULL constraint over a set of columns

While it's strictly "either/or" and the only student column is student_number, this query answers your question:

SELECT CASE WHEN student_number IS NULL THEN 'staff' ELSE 'student' END AS user_role
FROM   person
WHERE  person_id = (SELECT person_id FROM users WHERE user_number = 98242);

Or remove one or both CHECK constraints to allow the same person to be student and staff, or neither. Adapt above query accordingly.

You could use inheritance for this (like Abelisto demonstrates), but I'd rather stay away from it. There once was the idea of an object-relational DBMS. But the community has largely moved on. It works, but with caveats. Partitioning used to be a major use case. But declarative partitioning in Postgres 10 mostly superseded the inheritance-based implementation. There is not too much interest in it any more.

What about all those empty columns? Am I wasting a lot of space there? The opposite is the case. The disk footprint won't get much smaller than this. NULL storage is very cheap. See:

• Does not using NULL in PostgreSQL still use a NULL bitmap in the header?