CODEWITHSUNDEEP
sqldatabase-design
Nick
Nick

Reputation: 27996

Storing polymorphic objects in SQL database

[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:

Upvotes: 1

Views: 1504

Answers (3)

marc_s
marc_s

Reputation: 755471

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):

Hope this helps and gives you some inputs!

Marc

Upvotes: 3

Chris McCall
Chris McCall

Reputation: 10407

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.

Upvotes: 2

Andrew
Andrew

Reputation: 27324

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.

Upvotes: 1

Related Questions