Relational Data: entity inheritance approaches. Best practice

Question

There are several approaches how to store entities hierarchy in relation database

For example there is person entity (20 basic attributes), student entity (the same as person but several new specific fields are present), employee (the same as person but some new fields are present) e.t.c.

When you advice to use (and not to use) the following data modeling approaches:

• One big table with all possible fields + personType marker field (student or employee)
• Table inheritance
• One Table with XML field (or maybe another data type) to store all the custom fields
• Something else but also relational...

Thank you in advance!

Answer 1

A database models facts, not objects, and each table should model a relatively self-contained set of facts. The consequence of this is that your tables should look something like this:

person { person_id PK, name, dob, ... }
student { person_id PK FK(person.person_id), admission_id, year_started, ... }
employee { person_id PK FK(person.person_id), salary_bracket, ... }

An additional consequence is that a student can also be an employee, which probably models real life closer than an inheritance graph would.

Answer 2

Have a look at the hibernate inheritance mapping docs. There you find three common approaches and a list of pros and cons of each.