Help me to connect inheritance and relational concepts

Question

I was talking with a programmer pal of mine about inheritance and its use in designing models. He's a big proponent and I'm a little more tepid. Mostly because I tend to design systems from the bottom up: Database -> Application -> Presentation (honestly, I'm not much of a front-end guy, so I often leave presentation entirely to someone else). I find that relational database systems don't support inheritance without a lot of 1-to-1 relationships to other tables.

If I'm designing from a conceptual standpoint, an Administrator is a User is a Person. Starting from the database up, an Administrator is a User with UserType = "Administrator". Reconciling these approaches seems difficult to me, and therefore I only use inheritance with non-persisted objects.

What's wrong with my thinking? Why is inheritance such an oft-celebrated aspect of OO if it has these inherent incompatibilities with traditional relational structures? Or, is it me who's not mapping inheritance properly to relational data? Is there some guidance out there that might clear this up for me?

Sorry for the rambling question and thanks in advance for your answers. If you include code in your response, C# is my "native language".

Answer 1

There are many ways to express that kind of relationship in C#. Relational databases are simply more limited (which can be a benefit as well as a drawback). When you're designing an object hierarchy that's going to be persisted in a database, I find that it's usually easier to start with a database schema first, since virtually any database schema can be easily mapped to object relationships, whereas the opposite is not necessarily the case.

In this particular case, you might model the Administrator with composition. Your Administrators table would provide any additional admin state plus the key of the appropriate User. The Users FK would also be your Admins PK.

Answer 2

There are various kinds of inheritance. Developers may think in terms of interfaces and behaviour.

In your world what does it mean to have an a "userType" of administrator?

The semantics you mnight be implying are that some code somewhere sees that userType and authorises actions for users with that type. So perhaps an Administrator can create other Users?

In which case a developer might have a class User

class User() { viewData(){ ...} ; signReport() {...} }

and a class Adminstrator with an extra capability

class Administrator()  extends User {  createUser() {...}; approvePurchase() {...} }

An object of type Administrator can be used everywhere that a User can. This polymorphic behaviour can be helpful when implmenting. Now your database quite plausibly supports that case. However what will you do when Administrators need just a little bit extra data to carry out their specific task? For example, purchaseApproval() is up to a certain limit, we need that limit as a new field but it only applies to Administrators.

I believe that the behavioural aspects implied by your "type" concept quite often are associated with extra data.

Answer 3

This has been popularly termed the Object-Relational Impedance Mismatch:

Schema inheritance — Most relational databases do not support schema inheritance. Although such a feature could be added in theory to reduce the conflict with OOP, relational proponents are less likely to believe in the utility of hierarchical taxonomies and sub-typing because they tend to view set-based taxonomies or classification systems as more powerful and flexible than trees. OO advocates point out that inheritance/subtyping models need not be limited to trees (though this is a limitation in many popular OO languages such as Java), but non-tree OO solutions are seen as more difficult to formulate than set-based variation-on-a-theme management techniques preferred by relational. At the least, they differ from techniques commonly used in relational algebra.

See also: Agile Data, C2 Wiki

Answer 4

You should read the Patterns of Enterprise Application Architecture. It will show you how inheritance patterns can and should be expressed relational structures.

The important thing to remember is the inheritance is conceptual modelling thing. Relational structures and OO code are just materialisations of those conceptual models, and shouldn't be considered the be all and end all of object orientation.

There is an tutorial on this type of thing here.

Answer 5

The gulf between the relational paradigm and the OO paradigm is often discussed.

Basically, to provide a mapping between RDBMS and OO, you must sacrifice some capabilities of both systems. You have to decide which side your compromise is more heavily weighted toward.

Both paradigms are very powerful and useful. But trying to map between them seamlessly is an unresolved problem.

Answer 6

Yes, inheritance is probably not the right tool when you're mapping to and from the database.

Inheritance is much more useful when building frameworks, where you have different types of objects that behave differently, but still they have parts in common.

For example, you could have different "messages" passed between one server and another. Each messages has its own logic, so you need different classes (to avoid a huge switch() statement), but there's common parts, like the fact that every message needs to know how to serialize/deserialize itself into a stream (which is a method all subclasses of Message must override). Also, having all messages inherit Message will let you have a List, and go one by one and call their Serialize method, for example.

But if all you're doing is typical business/DB logic, inheritance will most likely stand in your way.

Answer 7

Why is inheritance such an oft-celebrated aspect of OO if it has these inherent incompatibilities with traditional relational structures?

This is a curious quote -- why do you say that relational structures are "traditional"? They're the norm in an RDBMS, by nature, but I've seen very few relational libraries in the context of a non-DB language.

The reason OO is popular is because it was significant improvement over the dominant procedural model. By using inheritance, code could be made more reliable (through type-checking), more easily modifiable (through method specializations) and more testable (through easy overriding of behaviors). Compared to a procedural model, OO is much easier to develop high-quality software with.

There is, of course, difficulty in mapping between the OO and relational models. This is called the "object-relational mismatch", and has been named "the Vietnam of computer science". There are various strategies for storing OO data in a relational database, but none are perfect -- the most popular in modern use are frameworks based on the active record pattern.

Answer 8

Normalized relational database schemas really do not support polymorphism unless you go outside of what are generally considered to be "best practices" in the world of the DBA. This is not a new issue by any means and the problem itself has been solved a thousand times over in several different ways.

The real issue comes into place when you begin to work with the data - if you are working with Datasets, you are going to continue to have challenges trying to implement any sort of polymorphism. If however you are mapping a set of domain entities to your relational model, you will find there are many tools to help you overcome the limitations of the relational database schema.

Since you're a C# developer, you should probably get started by looking into the following 2 projects, one of which comes with the latest .NET service pack.

ADO.NET Entity Framework:

http://msdn.microsoft.com/en-us/library/bb386876.aspx

NHibernate:

http://nhforge.org/Default.aspx

Answer 9

Your specific example of an Admininstrator being a User with a UserType of "Administrator" is a prime example of the "Single Table Inheritance" pattern. This is implemented in some of the major ORMs out there. Particularly "Rails ActiveRecord" and Hibernate.

Answer 10

I'd argue that the inheritance model in the question is flawed. It's too literal, or based in the real world. Yes, it could be argued that I am an Administrator in the real world and therefore an Administrator is a Person. However, object design and inheritance should be based more off of expressed and shared behaviors in the code space. I'd go more the route that a User has a Role. Administrator is a Role, or a an instance of a Role set to a certain state. A User is not a Person (your batch job may need a user account for example).

I recommend reading Object Thinking by David West for a good intro to object design. He doesn't cover the Object Relation Mismatch however, but people have already given many links to resources on that topic.

Answer 11

Hibernate simplifies inheritance. Example