Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Modelling inheritance in a database

For a database assignment I have to model a system for a school. Part of the requirements is to model information for staff, students and parents.

In the UML class diagram I have modelled this as those three classes being subtypes of a person type. This is because they will all require information on, among other things, address data.

My question is: how do I model this in the database (mysql)?

Thoughts so far are as follows:

  1. Create a monolithic person table that contains all the information for each type and will have lots of null values depending on what type is being stored. (I doubt this would go down well with the lecturer unless I argued the case very convincingly).
  2. A person table with three foreign keys which reference the subtypes but two of which will be null - in fact I'm not even sure if that makes sense or is possible?
  3. According to this wikipage about django it's possible to implement the primary key on the subtypes as follows:

    "id" integer NOT NULL PRIMARY KEY REFERENCES "supertype" ("id")
  4. Something else I've not thought of...

So for those who have modelled inheritance in a database before; how did you do it? What method do you recommend and why?

Links to articles/blog posts or previous questions are more than welcome.

Thanks for your time!

UPDATE

Alright thanks for the answers everyone. I already had a separate address table so that's not an issue.

Cheers,

Adam

like image 343
Adam Taylor Avatar asked Jan 06 '09 17:01

Adam Taylor


People also ask

What is modeling inheritance?

Inheritance is a design pattern commonly used in object-oriented programming which allows you to model entities that inherit certain properties or functions from a parent class. This can be incredibly useful for modeling entities which have multiple types, such as different types of activities to show in a feed.

What data model uses inheritance?

The Entity Data Model (EDM) supports inheritance for entity types. Inheritance in the EDM is similar to inheritance for classes in object-oriented programming languages.

What is inheritance in DBMS with example?

Inheritance is an important feature of Generalization and Specialization. It allows lower-level entities to inherit the attributes of higher-level entities. For example, the attributes of a Person class such as name, age, and gender can be inherited by lower-level entities such as Student or Teacher.

How inheritance is represented in ER diagram?

inheritance relationship in ERD should be represented as One-To-One relationship or One or Zero-to-One relationship depending on the case.


1 Answers

4 tables staff, students, parents and person for the generic stuff. Staff, students and parents have forign keys that each refer back to Person (not the other way around).

Person has field that identifies what the subclass of this person is (i.e. staff, student or parent).

EDIT:

As pointed out by HLGM, addresses should exist in a seperate table, as any person may have multiple addresses. (However - I'm about to disagree with myself - you may wish to deliberately constrain addresses to one per person, limiting the choices for mailing lists etc).

like image 146
Binary Worrier Avatar answered Sep 16 '22 15:09

Binary Worrier