sqldatabasedatabase-designclass-table-inheritance

How do we implement an IS-A Relationship?


We implement an One-to-Many relationship by adding one Table's PK, as FK to the other Table. We implement a Many-to-Many relationship by adding 2 Table's PKs to a third Table.

How do we implement an IS-A Relationship ?

The Entities are TECHNICIAN and ADMINISTRATIVE which both are EMPLOYEE. I could just use an extra field in the Table EMPLOYEE(id, name, surname, role, ...AdminFields..., ...TechFields...)

but i would like to explore the IS-A option.

EDIT: I did as Donnie suggested, but without the role field.


Solution

  • I did as Donnie suggested, but without the role field, because it complicates things. This is the final implementation:

    DDL:

    CREATE TABLE Employee (
    ast VARCHAR(20) not null,
    firstname VARCHAR(200) not null,
    surname VARCHAR(200) not null,
    ...
    PRIMARY KEY(ast)
    );
    
    CREATE TABLE Administrative (
    employee_ast VARCHAR(20) not null REFERENCES Employee(ast),
    PRIMARY KEY(employee_ast)
    );
    
    CREATE TABLE Technical (
    employee_ast VARCHAR(20) not null REFERENCES Employee(ast),
    ...
    PRIMARY KEY(employee_ast)
    );
    

    ER Diagram:

    ERD

    In this model there are no Employees of Generic Type. Here, an Employee can only be Administrative or Technical.