Storing polymorphic objects in SQL database

Question

[noob warning!] I need to store some data in some tables where it is like the equivalent of an array of pointers to polymorphic objects. E.g. (pseudo C++)

struct MyData { string name; }
struct MyDataA : MyData { int a,b,c; }
struct MyDataB : MyData { string s; }
MyData * data[100];

I don't really know what google search to enter! How would you store info like this in an SQL database?

My random thoughts:

• I could have one table with a column that is the struct identifier and then have redundant columns, but this seems wasteful.
• I can have one table for each struct type. These would have a foreign key back to the master array table. But, how do I point to the struct tables?

Answer 1

One possibility is an XML field to store the data, this allows searching and retrieving whilst also being relatively easy to serialise. (the question says SQL, but doesn't specify a specfic vendor database, so XML may not work for every DB solution.)

Edit : I'm going to caveat this because it's not entirely clear what needs to be stored / retrieved / purpose etc, so XML may be entirely inappropriate - I'm throwing it out there as a thought provoker instead.

Answer 2

This is a complex topic. To get an idea of the strategies involved, I suggest reading the Hibernate docs on this topic:

http://docs.jboss.org/hibernate/stable/core/reference/en/html/inheritance.html

Even if you're not using Hibernate, the relational mapping concepts are still relevant. Note that Hibernate does not dictate any one strategy, because no one approach is better than the others.

Answer 3

I do the "table-per-sublcass" style from the Hibernate docs.

You make a Person table with all the things you know about a person, plus the PersonID. Then you make a Customer table, with only the data that's unique to a Customer (account balance, etc). Put the PersonID in the Customer table. A WebsiteUser might have a CustomerID in it, and so on down the chain.

One-to-one relationships mapping the IS-A inheritance relationships.

Answer 4

Here's your google search:

http://www.google.com/search?q=object+relational+modeling&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a

Answer 5

There's really two major ways to solve this:

• table-per-type
• table-per-hierarchy

Either of them has its pros and cons.

Table-per-type gives you more tables (one per type), which only store the "delta" from the immediate super class. Worst case, you need to join together a number of tables to finally get all the data together for a single instance of a type. Pros: since you only store what's really relevant for that type into a separate table, you can do this like set NOT NULL restrictions etc. on the database table.

Table-per-hierarchy gives you less tables, but each table represents an entire hierarchy, so it will contains potentially lots of columns which aren't filled (in the rows representating base class types). Also, on the extra columns that make up the derived classes, you cannot set things like NOT NULL restrictions - all those extra columns must be nullable, since they really don't exist in the base classes, so you loose some degree of safety here.

See for yourself - there are two really good articles on how to do this (in Entity Framework, but the principles apply to any database and any data mapping technology):

• Demystifying The Code: Table Per Type
• Demystifying The Code: Table Per Hierarchy

Hope this helps and gives you some inputs!

Marc