How do you deal with polymorphism in a database?

Question

Example

I have Person, SpecialPerson, and User. Person and SpecialPerson are just people - they don't have a user name or password on a site, but they are stored in a database for record keeping. User has all of the same data as Person and potentially SpecialPerson, along with a user name and password as they are registered with the site.

---

How would you address this problem? Would you have a Person table which stores all data common to a person and use a key to look up their data in SpecialPerson (if they are a special person) and User (if they are a user) and vice-versa?

Answer 1

Take a look at Martin Fowler's Patterns of Enterprise Application Architecture:

• Single Table Inheritance:

  When mapping to a relational database, we try to minimize the joins that can quickly mount up when processing an inheritance structure in multiple tables. Single Table Inheritance maps all fields of all classes of an inheritance structure into a single table.

• Class Table Inheritance:

  You want database structures that map clearly to the objects and allow links anywhere in the inheritance structure. Class Table Inheritance supports this by using one database table per class in the inheritance structure.

• Concrete Table Inheritance:

  Thinking of tables from an object instance point of view, a sensible route is to take each object in memory and map it to a single database row. This implies Concrete Table Inheritance, where there's a table for each concrete class in the inheritance hierarchy.

Answer 2

There are generally three ways of mapping object inheritance to database tables.

You can make one big table with all the fields from all the objects with a special field for the type. This is fast but wastes space, although modern databases save space by not storing empty fields. And if you're only looking for all users in the table, with every type of person in it things can get slow. Not all or-mappers support this.

You can make different tables for all the different child classes with all of the tables containing the base-class fields. This is ok from a performance perspective. But not from a maintenance perspective. Every time your base-class changes all the tables change.

You can also make a table per class like you suggested. This way you need joins to get all the data. So it's less performant. I think it's the cleanest solution.

What you want to use depends of course on your situation. None of the solutions is perfect so you have to weigh the pros and cons.

Answer 3

What I'm going to say here is going to send database architects into conniptions but here goes:

Consider a database view as the equivalent of an interface definition. And a table is the equivalent of a class.

So in your example, all 3 person classes will implement the IPerson interface. So you have 3 tables - one for each of 'User', 'Person' and 'SpecialPerson'.

Then have a view 'PersonView' or whatever that selects the common properties (as defined by your 'interface') from all 3 tables into the single view. Use a 'PersonType' column in this view to store the actual type of the person being stored.

So when you're running a query that can be operated on any type of person, just query the PersonView view.

Answer 4

If the User, Person and Special person all have the same foreign keys, then I would have a single table. Add a column called Type which is constrained to be User, Person or Special Person. Then based on the value of Type have constraints on the other optional columns.

For the object code it doesn't make much difference if you have the separate tables or multiple tables to represent polymorphism. However if you have to do SQL against the database, its much easier if the polymorphism is captured in single table...provided the foreign keys for the sub types are the same.

Answer 5

There's three basic strategies for handling inheritance in a relational database, and a number of more complex/bespoke alternatives depending on your exact needs.

• Table per class hierarchy. One table for the whole hierarchy.
• Table per subclass. A separate table is created for every sub class with a 0-1 association between the subclassed tables.
• Table per concrete class. A single table is created for every concrete class.

Each of these appoaches raises its own issues about normalization, data access code, and data storage, although my personal preferance is to use table per subclass unless there's a specific performance or structural reason to go with one of the the alternatives.

Answer 6

At the risk of being an 'architecture astronaut' here, I would be more inclined to go with separate tables for the subclasses. Have the primary key of the subclass tables also be a foreign key linking back to the supertype.

The main reason for doing it this way is that it then becomes much more logically consistent and you do not end up with a lot of fields that are NULL and nonsensical for that particular record. This method also makes it much more easy to add extra fields to the subtypes as you iterate your design process.

This does add the downside of adding JOINs to your queries, which can impact performance, but I almost always go with an ideal design first, and then look to optimise later if it proves to be necessary. The few times I have gone the 'optimal' way first I have almost always regretted it later.

So my design would be something like

PERSON (personid, name, address, phone, ...)

SPECIALPERSON (personid REFERENCES PERSON(personid), extra fields...)

USER (personid REFERENCES PERSON(personid), username, encryptedpassword, extra fields...)

You could also create VIEWs later on that aggregates the supertype and the subtype, if that is necessary.

The one flaw in this approach is if you find yourself heavily searching for the subtypes associated with a particulare supertype. There is no easy answer to this off the top of my head, you could track it programmatically if necessary, or else run soem global queries and cache the results. It will really depend on the application.

Answer 7

This might not be what the OP meant to ask, but I thought I might throw this in here.

I recently had a unique case of db polymorphism in a project. We had between 60 to 120 possible classes, each with its own set of 30 to 40 unique attributes, and about 10 - 12 common attributes on all the classes . We decided to go the SQL-XML route and ended up with a single table. Something like :

PERSON (personid,persontype, name,address, phone, XMLOtherProperties)

containing all common properties as columns and then a big XML property bag. The ORM layer was then responsible for reading/writing the respective properties from the XMLOtherProperties. A bit like :

 public string StrangeProperty
{
get { return XMLPropertyBag["StrangeProperty"];}
set { XMLPropertyBag["StrangeProperty"]= value;}
}

(we ended up mapping the xml column as a Hastable rather than a XML doc, but you can use whatever suits your DAL best)

It's not going to win any design awards, but it will work if you have a large (or unknown) number of possible classes. And in SQL2005 you can still use XPATH in your SQL queries to select rows based on some property that is stored as XML.. it's just a small performance penalty to take in.

Answer 8

In our company we deal with polymorphism by combining all the fields in one table and its worst and no referential integrity can be enforced and very difficult to understand model. I would recommend against that approach for sure.

I would go with Table per subclass and also avoid performance hit but using ORM where we can avoid joining with all subclass tables by building query on the fly by basing on type. The aforementioned strategy works for single record level pull but for bulk update or select you can't avoid it.

Answer 9

I'd say that, depending on what differentiates Person and Special Person, you probably don't want polymorphism for this task.

I'd create a User table, a Person table that has a nullable foreign key field to User (i.e, the Person can be a User, but does not have to).
Then I would make a SpecialPerson table which relates to the Person table with any extra fields in it. If a record is present in SpecialPerson for a given Person.ID, he/she/it is a special person.

Answer 10

This is an older post but I thought I'll weigh in from a conceptual, procedural and performance standpoint.

The first question I would ask is the relationship between person, specialperson, and user, and whether it's possible for someone to be both a specialperson and a user simultaneously. Or, any other of 4 possible combinations (class a + b, class b + c, class a + c, or a + b + c). If this class is stored as a value in a type field and would therefore collapse these combinations, and that collapse is unacceptable, then I would think a secondary table would be required allowing for a one-to-many relationship. I've learned you don't judge that until you evaluate the usage and the cost of losing your combination information.

The other factor that makes me lean toward a single table is your description of the scenario. User is the only entity with a username (say varchar(30)) and password (say varchar(32)). If the common fields' possible length is an average 20 characters per 20 fields, then your column size increase is 62 over 400, or about 15% - 10 years ago this would have been more costly than it is with modern RDBMS systems, especially with a field type like varchar (e.g. for MySQL) available.

And, if security is of concern to you, it might be advantageous to have a secondary one-to-one table called credentials ( user_id, username, password). This table would be invoked in a JOIN contextually at say time of login, but structurally separate from just "anyone" in the main table. And, a LEFT JOIN is available for queries that might want to consider "registered users".

My main consideration for years is still to consider the object's significance (and therefore possible evolution) outside the DB and in the real world. In this case, all types of persons have beating hearts (I hope), and may also have hierarchical relationships to one another; so, in the back of my mind, even if not now, we may need to store such relationships by another method. That's not explicitly related to your question here, but it is another example of the expression of an object's relationship. And by now (7 years later) you should have good insight into how your decision worked anyway :)

Answer 11

yes, I would also consider a TypeID along with a PersonType table if it is possible there will be more types. However, if there is only 3 that shouldn't be nec.

Answer 12

In the past I've done it exactly as you suggest -- have a Person table for common stuff, then SpecialPerson linked for the derived class. However, I'm re-thinking that, as Linq2Sql wants to have a field in the same table indicate the difference. I haven't looked at the entity model too much, though -- pretty sure that allows the other method.

Answer 13

Personally, I would store all of these different user classes in a single table. You can then either have a field which stores a 'Type' value, or you can imply what type of person you're dealing with by what fields are filled in. For example, if UserID is NULL, then this record isn't a User.

You could link out to other tables using a one to one-or-none type of join, but then in every query you'll be adding extra joins.

The first method is also supported by LINQ-to-SQL if you decide to go down that route (they call it 'Table Per Hierarchy' or 'TPH').