Modelling inheritance in a database

Question

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

Answer 1

Super type Person should be created like this:

CREATE  TABLE Person(PersonID int primary key, Name varchar ... etc ...)

All Sub types should be created like this:

CREATE  TABLE IF NOT EXISTS Staffs(StaffId INT NOT NULL ,
  PRIMARY KEY (StaffId) ,  
  CONSTRAINT FK_StaffId FOREIGN KEY (StaffId) REFERENCES Person(PersonId)
  )

CREATE  TABLE IF NOT EXISTS Students(StudentId INT NOT NULL ,
  PRIMARY KEY (StudentId) ,  
  CONSTRAINT FK_StudentId FOREIGN KEY (StudentId) REFERENCES Person(PersonId)
  )

CREATE  TABLE IF NOT EXISTS Parents(PersonID INT NOT NULL ,
  PRIMARY KEY (PersonID ) ,  
  CONSTRAINT FK_PersonID  FOREIGN KEY (PersonID ) REFERENCES Person(PersonId)
  )

Foreign key in subtypes staffs,students,parents adds two conditions:

1. Person row cannot be deleted unless corresponding subtype row will not be deleted. For e.g. if there is one student entry in students table referring to Person table, without deleting student entry person entry cannot be deleted, which is very important. If Student object is created then without deleting Student object we cannot delete base Person object.

---

1. All base types have foreign key "not null" to make sure each base type will have base type existing always. For e.g. If you create Student object you must create Person object first.

Answer 2

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).

Answer 3

I suggest five tables Person Student Staff Parent Address

WHy - because people can have multiple addesses and people can also have multiple roles and the information you want for staff is different than the information you need to store for parent or student.

Further you may want to store name as last_name, Middle_name, first_name, Name_suffix (like jr.) instead of as just name. Belive me you willwant to be able to search on last_name! Name is not unique, so you will need to make sure you have a unique surrogate primary key.

Please read up about normalization before trying to design a database. Here is a source to start with: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Answer 4

The "correct" answer for the purposes of an assignment is probably #3 :

Person
PersonId Name Address1 Address2 City Country

Student
PersonId StudentId GPA Year ..

Staff
PersonId StaffId Salary ..

Parent
PersonId ParentId ParentType EmergencyContactNumber ..

Where PersonId is always the primary key, and also a foreign key in the last three tables.

I like this approach because it makes it easy to represent the same person having more than one role. A teacher could very well also be a parent, for example.

Answer 5

I would go for #3.

Your goal is to impress a lecturer, not a PM or customer. Academics tend to dislike nulls and might (subconciously) penalise you for using the other methods (which rely on nulls.)

And you don't necessarily need that django extension (PRIMARY KEY ... REFERENCES ...) You could use an ordinary FOREIGN KEY for that.

Answer 6

OO databases go through the same stuff and come up with pretty much the same options.

If the point is to model subclasses in a database, you probably are already thinking along the lines of the solutions I've seen in real OO databases (leaving fields empty).

If not, you might think about creating a system that doesn't use inheritance in this way.

Inheritance should always be used quite sparingly, and this is probably a pretty bad case for it.

A good guideline is to never use inheritance unless you actually have code that does different things to the field of a "Parent" class than to the same field in a "Child" class. If business code in your class doesn't specifically refer to a field, that field absolutely shouldn't cause inheritance.

But again, if you are in school, that may not match what they are trying to teach...

Answer 7

Well I think all approaches are valid and any lecturer who marks down for shoving it in one table (unless the requirements are specific to say you shouldn't) is removing a viable strategy due to their own personal opinion.

I highly recommend that you check out the documentation on NHibernate as this provides different approaches for performing the above. Which I will now attempt to poorly parrot.

Your options:

• 1) One table with all the data that has a "delimiter" column. This column states what kind of person the person is. This is viable in simple scenarios and (seriously) high performance where the joins will hurt too much
• 2) Table per class which will lead to duplication of columns but will avoid joins again, so its simple and a lil faster (although only a lil and indexing mitigates this in most scenarios).

• 3) "Proper" inheritence. The normalised version. You are almost there but your key is in the wrong place IMO. Your Employee table should contain a PersonId so you can then do:

  select employee.id, person.name from employee inner join person on employee.personId = person.personId

To get all the names of employees where name is only specified on the person table.

Answer 8

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

Methods 1 and 3 are good. The differences are mostly in what your use cases are.

1) adaptability -- which is easier to change? Several separate tables with FK relations to the parent table.

2) performance -- which requires fewer joins? One single table.

Rats. No design accomplishes both.

Also, there's a third design in addition to your mono-table and FK-to-parent.

Three separate tables with some common columns (usually copy-and-paste of the superclass columns among all subclass tables). This is very flexible and easy to work with. But, it requires a union of the three tables to assemble an overall list.