40

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.

Eric Lavoie
  • 5,121
  • 3
  • 32
  • 49
athspk
  • 6,722
  • 7
  • 37
  • 51
  • 1
    IS-A is not a *relationship* per se. A row's identity is typically defined by the name of the table that contains it. eg. A row in a table called `employees` IS-A employee. If you want to add an `employee_type` enumueration containing `TECHINICIAN`, `ADMINISTRATIVE`, etc., that's fine. But each row in that table still IS-A employee. – Asaph Dec 05 '10 at 21:48
  • 2
    @Asaph - I took this to mean the mapping onto subclasses, via an ORM for example. In this case Technician would be a subclass of Employee in the domain model and there would be an IS-A relationship between Technician and Employee entities. That said, I don't think roles in an organization are best modeled using inheritance. – tvanfosson Dec 05 '10 at 21:54
  • 1
    @Asaph - What you do if TECHNICIAN has many specifics properties, that ADMINISTRATIVE has not (and reverse) ? – Eric Lavoie Jun 18 '14 at 14:01

8 Answers8

30

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.

athspk
  • 6,722
  • 7
  • 37
  • 51
  • 1
    I believe that keeping a role column is useful for when you want to downcast an employee into it's real implementation. If you had an employee ID, and you wanted to retrieve all of his information. You'd have to know with which ROLE table to join. Without the role column, you will need to lookup each ROLE's table to see if the ID exists in it, then join on it. – Didier A. Mar 06 '13 at 16:43
  • Or similarly, given an employee ID, retrieve what role he has in the company. I do feel like it's hard to make sure the integrity of the role column in relation to the role table is hard to maintain. I wonder if there is a better way to do this. – Didier A. Mar 06 '13 at 16:46
  • Better way: create view vwEmployeeRoles as select id, "technician" as role from technicians union select id, "administrator" as role from administrators – naomi Mar 07 '13 at 15:56
8

The IS-A relationship is also known as the gen-spec design pattern. Gen-spec is short for "generalization specialization".

Relational modeling of gen-spec is different from object modeling of gen-spec because the relational model doesn't have inheritance built in.

Here is a good article that shows how to implement gen-spec as a collection of tables.

http://www.javaguicodexample.com/erdrelationalmodelnotes1.html

Pay particular attention to the way primary keys are set up in the specialized tables. That's what makes using these tables so easy.

You can find lots of other articles by googlin "generalization specialization relational modeling".

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
7

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.

Donnie
  • 45,732
  • 10
  • 64
  • 86
  • when i add 'role' in EMPLOYEE entity, and other table ROLE(employeeid,roleType), let's say two is 2 data only 'W' for Worker. 'T' for technician , this it kind of redundancy ?? – Yassine Abdul-Rahman May 17 '14 at 20:47
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.
Don Roby
  • 40,677
  • 6
  • 91
  • 113
  • A non disjoint IS-A relationship can have multiple roles. You could just have the employee ID be in both the Administrative table and the Technical table. At least I believe so. How would you handle the HAS-A relationship instead, considering that each role has a different set of attributes? Would you create one table for each role? But then how would you reference that from the Employee table? – Didier A. Mar 06 '13 at 16:53
3

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.

Ronnis
  • 12,593
  • 2
  • 32
  • 52
2

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.

mattmc3
  • 17,595
  • 7
  • 83
  • 103
1

It depends if you are building a mono-hierarchy or poly-hierarchy. This is a hard coded design, which I believe is what you wanted.

For mono (child table has one parent table), where child IS-A parent, the FK and PK is the same in the child table, and that key is also the PK in the parent table.

For poly (child table has multiple parent tables), where child IS-A parent-1 and child IS-A parent-2, you'll have a composite key (meaning multiple primary keys to make table record unique), where the rule is the same as a mono-hierarchy for each key.

JustBeingHelpful
  • 18,332
  • 38
  • 160
  • 245
1

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.

tvanfosson
  • 524,688
  • 99
  • 697
  • 795
  • Hibernate has support for several different mappings for inheritance, including this single-table-with-discriminator strategy, but not limited to it. I agree that role is better modeled as HAS-A. – Don Roby Dec 05 '10 at 22:05
  • How would you handle the HAS-A relationship instead, considering that each role has a different set of attributes? Would you create one table for each role? But then how would you reference that from the Employee table? – Didier A. Mar 06 '13 at 16:57
  • 1
    @didibus - using a join table with foreign keys for person and role, allowing each person to have multiple roles. If you needed flexibility for permissions (attributes?), you could use another join table between roles and permissions. – tvanfosson Mar 07 '13 at 00:25