Inheritance in table creation in SQL

Question

I want to create Worker and Student tables which contains a lot of similar information (First name, second name etc.), so I'm going to extract another like abstract table named Person, which will contain general information for Worker and Student tables.

Which is the best way to do that?

Also I need that ids will be unique i.e. there isn't the same id in Worker table and Student table.

score 0 · Accepted Answer · answered May 17 '20 at 15:38

Create a table persons with all the combined information and a person_id.

Create two additional tables, workers and students. The primary keys for these tables are foreign keys to the persons table:

create table students (
    student_id int primary key references persons (person_id),
    . . .
);

create table workers (
    worker_id int primary key references persons (person_id),
    . . .
);

Inheritance in table creation in SQL

1 Answers1