How do we implement an IS-A Relationship?

sql database database-design class-table-inheritance
athspk picture athspk · Dec 5, 2010 · Viewed 61.3k times · Source

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.

Answer

athspk picture athspk · Jan 12, 2011

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.

Related questions

How to create a new schema/new user in Oracle Database 11g?

I have applied for an internship in a company and as a question they have asked me to create a schema for their company with certain requirements and mail them the DDL file. I have installed Oracle database 11g Express …

Read More
SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?

If I have two relations in a database, like this: CREATE TABLE Courses ( CourseID int NOT NULL PRIMARY KEY, Course VARCHAR(63) NOT NULL UNIQUE, Code CHAR(4) NOT NULL UNIQUE ); CREATE TABLE BookCourses ( EntryID int NOT NULL PRIMARY KEY, BookID int …

Read More
A beginner's guide to SQL database design

Do you know a good source to learn how to design SQL solutions? Beyond the basic language syntax, I'm looking for something to help me understand: What tables to build and how to link them How to design for different …

Read More