Abstract Class from UML to ER diagram. Possible ? How?

Question

I have the below UML class diagram with Abstract Class, and sub-Classes that extends from it. and i want to make an ER diagram using this class diagram.

My question is how can i represent the Abstract class in ER diagram ? as a Table ? or should i just ignore it ?

Thank you.

Answer 1

There are basically three choices to translate generalization into a database model

1. One table per concrete class

Create tables Admin, Teacher and Student. Each of these table contain columns for all of the attributes and relations of User

• Pro
  • All fields of a concrete subclass are in the same table, so no join needed to get all Student data
  • Easy data validation constraints (such as mandatory fields for Student)
• Con
  • All fields of User are duplicated in each subclass table
  • Foreign keys to User have to be split into three FK fields. One for Admin, one for Teacher and one for Student.

2. On table for whole generalization set

In this case you just have one table call User that contains all fields of User + all fields of all sub-classes of User

• Pro
  • All fields are in the same table, so no join needed to get all User data
  • No splitting of FK's to User
• Con
  • There are a bunch of fields that are never used. All fields specific for Student and Teacher are never filled in for Admins and vice versa
  • Data validation such as mandatory fields for a concrete class such as Student become rather complex as it is no longer a simple Not Null constraint.

3. One table per concrete class, and one for the superclass

In this case you create tables for each of the concrete sub-classes and you create a table for the class User. Each of the concrete sub-class tables has a mandatory FK to User

• Pro
  • Most normalized schema: No repeated fields for the attributes of user, and no unused fields.
  • No splitting of FK's to User
  • Easy data validation constraints (such as mandatory fields for Student)
• Con
  • You have to query two tables if you want all data of a Student
  • Complex validation rules to make sure each User record has exactly one Admin, Teacher or Student record.

Which one of these options you choose depends on a number of things such as the number of sub-classes, the number of attributes in either subclass or superclass, the number of FK's to the superclass, and probably a few other things I didn't think about.