3

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.

enter image description here

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

Thank you.

hamza saber
  • 511
  • 1
  • 4
  • 18
  • 1
    To be honest it looks the same with out . see the examples here where you can also make an erd online https://online.visual-paradigm.com/drive/#diagramlist:proj=0&new=ERDiagram also look here https://stackoverflow.com/questions/48191228/is-erd-considered-a-kind-of-uml-diagram – nbk Sep 06 '19 at 22:34
  • 1
    You might also have a design flaw here. What if a teacher is also an Admin? In your design that is not possible. I think you should separate Users from the roles they play. – Geert Bellekens Sep 08 '19 at 07:27
  • @GeertBellekens No that's not the case here. I already know that one user can either be an Admin, Teacher or a student – hamza saber Sep 08 '19 at 17:19

1 Answers1

7

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.

Geert Bellekens
  • 12,788
  • 2
  • 23
  • 50
  • I've accepted your answer since it enlights me to search more on this, and as you said these are the only 3 methods to perform generalization. As a result, i've chosen the 3rd method as it works well for my requirements (not that it is the best method)., – hamza saber Sep 08 '19 at 17:21