Consider this example (they are disjoint, by the way):

enter image description here

There are two scenarios which I am guessing:

  1. While translating it to relational tables, the rows in PROGRAMMER are unique which do not exist in EMPLOYEE table and it has a PRIMARY KEY

  2. They already exist in EMPLOYEE table and the PROGRAMMER has a foreign key which references it.

Which scenario is correct? or both are wrong?


It's not an either / or situation. The answer for your particular system may exists as a combination of these two options. Let me explain.

If the only attribute in common between the Programmer, Engineer and Admin tables are the Emp# then I'd likely implement them as separate tables. Each would have Emp# as the primary key. I would not implement the Employee table at all since it added no additional value to the run-time system.

If the three sub-types had many columns in common, say Name, HireDate, Department .. HolidayBalance, ContactNumber etc., and only a few sub-type specific values (as you've annotated on your diagram) you could put all columns in Employee and make the inapplicable ones (e.g. a Programmer's EType) NULL. A view can be defined for each sub-type if excluding the NULLs becomes tedious.

Likely the truth lies somewhere between these two - there are enough specific attributes in each sub-type to warrant its own table, but enough common ones to justify an Employee table, too. In this case I'd make Emp# the primary key of Employee and also each sub-type would have the primary key of Emp#, too. Further I'd define Programmer.Emp# to be a foreign key referencing Employee.Emp#, and the same for the other sub-types. In this way uniqueness is maintained within each sub-type and so is integrity between the sub-type and super-type. In other words, I'd make a one-to-one relationship between Employee and each sub-type.

I would not implement a surrogate key in each of the sub-types (for example ProgrammerId) as it adds nothing that Emp# can't already achieve.

Note that it is difficult to declaratively ensure mutual exclusion between the sub-types. This will be enforced in the application or through triggers.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.