How do you effectively model inheritance in a data

Question

What are the best practices for modeling inheritance in databases?

What are the trade-offs (e.g. queriability)?

(I'm most interested in SQL Server and .NET, but I also want to understand how other platforms address this issue.)

Answer 1

Proper database design is nothing like proper object design.

If you are planning to use the database for anything other than simply serializing your objects (such as reports, querying, multi-application use, business intelligence, etc.) then I do not recommend any kind of a simple mapping from objects to tables.

Many people think of a row in a database table as an entity (I spent many years thinking in those terms), but a row is not an entity. It is a proposition. A database relation (i.e., table) represents some statement of fact about the world. The presence of the row indicates the fact is true (and conversely, its absence indicates the fact is false).

With this understanding, you can see that a single type in an object-oriented program may be stored across a dozen different relations. And a variety of types (united by inheritance, association, aggregation, or completely unaffiliated) may be partially stored in a single relation.

It is best to ask yourself, what facts do you want to store, what questions are you going to want answers to, what reports do you want to generate.

Once the proper DB design is created, then it is a simple matter to create queries/views that allow you to serialize your objects to those relations.

Example:

In a hotel booking system, you may need to store the fact that Jane Doe has a reservation for a room at the Seaview Inn for April 10-12. Is that an attribute of the customer entity? Is it an attribute of the hotel entity? Is it a reservation entity with properties that include customer and hotel? It could be any or all of those things in an object oriented system. In a database, it is none of those things. It is simply a bare fact.

To see the difference, consider the following two queries. (1) How many hotel reservations does Jane Doe have for next year? (2) How many rooms are booked for April 10 at the Seaview Inn?

In an object-oriented system, query (1) is an attribute of the customer entity, and query (2) is an attribute of the hotel entity. Those are the objects that would expose those properties in their APIs. (Though, obviously the internal mechanisms by which those values are obtained may involve references to other objects.)

In a relational database system, both queries would examine the reservation relation to get their numbers, and conceptually there is no need to bother with any other "entity".

Thus, it is by attempting to store facts about the world—rather than attempting to store entities with attributes—that a proper relational database is constructed. And once it is properly designed, then useful queries that were undreamt of during the design phase can be easily constructed, since all the facts needed to fulfill those queries are in their proper places.

Answer 2

There are two main types of inheritance you can setup in a DB, table per entity and table per Hierarchy.

Table per entity is where you have a base entity table that has shared properties of all child classes. You then have per child class another table each with only properties applicable to that class. They are linked 1:1 by their PK's

alt text http://mattlant.com/ent.png

Table per hierarchy is where all classes shared a table, and optional properties are nullable. Their is also a discriminator field which is a number that denotes the type that the record currently holds

alt text http://mattlant.com/hier.png SessionTypeID is discriminator

Target per hierarchy is faster to query for as you do not need joins(only the discriminator value), whereas target per entity you need to do complex joins in order to detect what type something is as well as retreiuve all its data..

Edit: The images I show here are screen shots of a project I am working on. The Asset image is not complete, hence the emptyness of it, but it was mainly to show how its setup, not what to put inside your tables. That is up to you ;). The session table holds Virtual collaboration session information, and can be of several types of sessions depending on what type of collaboration is involved.

Answer 3

repeat of similar thread answer

in O-R mapping, inheritance maps to a parent table where the parent and child tables use the same identifier

for example

create table Object (
    Id int NOT NULL --primary key, auto-increment
    Name varchar(32)
)
create table SubObject (
    Id int NOT NULL  --primary key and also foreign key to Object
    Description varchar(32)
)

SubObject has a foreign-key relationship to Object. when you create a SubObject row, you must first create an Object row and use the Id in both rows

EDIT: if you're looking to model behavior also, you would need a Type table that listed the inheritance relationships between tables, and specified the assembly and class name that implemented each table's behavior

seems like overkill, but that all depends on what you want to use it for!