0

Conceptual Model | I have not been able to find any example of a subclass with it's own PK. I understand that the primary key, person_id, is inherited from the superclass, but I do not know whether it combines with the subclass' PK, employee_id, to create a composite PK in the subclass' relational model.

Basically, which is correct?

Employee(employee_id, person_id, name, salary)

OR

Employee(employee_id, person_id, name, salary)

Assuming the hierarchy is not mandatory.

This is just a quick example to help me understand the process, so don't worry about the goodness of the conceptual model.

  • 1
    The premise is flawed. A generalization must hold true in all cases, but a Person is not always an Employee. Having a role (Employee) specialize a Person is also not a good idea. They should be two separate tables. – Jim L. Mar 18 '17 at 22:27

2 Answers2

4

First, as pointed out by Jim L, Employee is a subtype of Person, and not the other way around!

Conceptually, if an object type B is a subtype/subclass of A, it inherits its properties, methods and constraints, including its unique identifiers/keys, but not necessarily its standard ID ("PK") because a PK is an arbitrary choice of a mandatory key, so it's not a purely conceptual/logical feature, but rather a user declaration/convention.

Keys, as constraints, are inherited, but not PKs!

Therefore, even if the subtype Employee inherits the mandatory key person_id, you don't have to choose it as its PK. Since Employee happens to have another mandatory key, employee_id, you may choose this one as the PK (and, as pointed out by reaanb, there is no need for any composite PK in this example).

Gerd Wagner
  • 5,481
  • 1
  • 22
  • 41
0

A composite PK is a bad idea - it would allow multiple rows with the same employee_id and different person_id (or the same person_id and different employee_id) to be recorded. Rather make employee_id the PK and person_id a separate unique key.

Conceptually, when a subtype has its own identity, it can be viewed as a distinct entity set with a relationship to the parent entity set, rather than a subtype. I wouldn't use subtyping notation in an EER diagram to represent these situations.

Finally, please avoid the use of OOP terminology (subclassing, inheritance) when discussing data modeling. OOP is for decomposing systems in terms of communicating state machines, and shouldn't be conflated with hierarchical, network, entity-relationship or relational data models.

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • There is no need to avoid OO modeling terminology because OO/UML class diagrams just provide a data/information modeling language that is more convenient (and more expressive) than EER diagrams. – Gerd Wagner May 08 '17 at 08:22
  • @GerdWagner Class diagrams are for systems modeling, not for data modeling. ER and relational models support n-ary relationships and provide techniques to manage dependencies and data integrity. OOP encapsulates and abstracts over state and supports binary directed associations at best, and have absolutely no built-in data integrity. – reaanb May 08 '17 at 09:52
  • @raanb: You are just repeating an obsolete credo of data modeling guys. Class diagrams provide a rich visual modeling language that can be used for information, data and class modeling. Just take a look at http://web-engineering.info/JavaJpaJsfApp-Book or at the papers of the ER Conference (http://er2016.cs.titech.ac.jp/) to get up-to-date. – Gerd Wagner May 09 '17 at 11:28
  • @GerdWagner The book you linked describes everything I rejected in favor of a deeper understanding of OOP and data modeling. Thanks, but no thanks. – reaanb May 09 '17 at 12:08
  • @raanb: Your "deeper understanding" seems to be wishful thinking. – Gerd Wagner May 09 '17 at 12:23
  • @GerdWagner The linked book reinvents a pre-relational network data model which is commonly (and mistakenly) called an object data model. OOP works for interacting state machines (it had its origins in simulation and computational systems) - using it for data modeling violates encapsulation, favors navigational data access over set-based operations; and doesn't provide the expressiveness or logic of the relational model nor any of the features of a DBMS. I appreciate your comments but I stand by what I said. – reaanb May 16 '17 at 16:27