How do we implement an IS-A Relationship?

Question

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 1

I've always done this with a role field, and then optional relationships.

I.e., table EMPLOYEE (id, ...generic fields... , role)

And then, for each role:

table ROLE1 (employeeid, ...specific fields...)

This allows you to get general employee information with a single query, and requires joins to get at the role-specific information. The one (bigish) downside to this is if you need one super-report with all of the role information on it you get stuck with a bunch of outer joins.

Answer 2

This paper describes some strategies for mapping generalizations to into schema design.

http://www.sztaki.hu/conferences/ADBIS/3-Eder.pdf

A copy of the abstract:

The richer data models of object relational databases opens many more options for the logical design of a database schema increasing the complexity of logical database design enormously. Focusing on generalization constructs of conceptual models we explore the performance implications of the various design alternatives for mapping generalizations into the schema of an object-relational database system.

Answer 3

Why not implement this as a one-to-zero/one table relationship? Let's say you have a table representing a base class called Vehicle, with a primary key of VehicleID. Then, you can have any number of satellite tables representing all the subclasses of Vehicle, and those tables also have VehicleID as their primary key, having a 1->0/1 relationship from Vehicle->Subclass.

Or, if you want to make it simpler and you know for sure that you'll only ever have a few sub classes and there's not much chance of that changing, you could just represent the whole structure in a single table with a discriminator type field.

Answer 4

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:

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

Answer 5

If you have an OO application that you need to connect to a relational back-end database, I'd recommend getting Martin Fowler's Patterns of Enterprise Application Architecture.

He also has some relevant notes and diagrams on his website. Specifically, the patterns Single Table Inheritance, Class Table Inheritance and Concrete Table Inheritance describe three tactics for mapping IS-A in data tables.

If you're using Hibernate or JPA, they support mappings for all of these, though they have different names for them.

In this specific instance, I wouldn't use IS-A at all though.

Things like employee roles are better modeled as HAS-A, as

1. You might want a single person to have multiple roles.
2. Changing a person's role will be easier.

Answer 6

Most ORMs implement the IS-A relationship using a single column discriminator, choosing which subclass to instantiate based on the value in a particular column. With respect to your example, you probably don't really mean role, since typically a person can fill many different types of roles. Roles would typically be modeled as a has-a relationship. If you do try to implement it using is-a relationships (or subclassing) you inevitably end up having to do something more complicated to handle cases where you have a person filling a hybrid position -- i.e., a secretary who also functions as the local IT person, needing permissions or attributes of both.