Model Heterogeneous Type in Database

Question

I am trying to figure out the best way to model a set of "classes" in my system. Note that I'm not talking about OO classes, but classes of responses (in a survey). So the model goes like this:

A Class can be defined with three different types of data:

• A Class of Coded Responses (where a coded responses consists of a string label and an integer value)

• A Class of Numeric Responses (defined as a set of intervals where each interval ranges from a min to a max value)

• A Class of String Responses (defined as a set of regular expression patterns)

Right now we have: Class table (to define unique classes) and a ClassCoded, ClassNumeric and ClassString table (all with a ClassID as a foreign key to Class table).

My problem is that right now a Class could technically be both Coded and Numeric by this system. Is there any way to define the set of tables to be able to handle this situation??

Answer 1

Relational databases don't handle this elegantly. Simplest way is to define columns for all different types of data, and only fill the appropriate ones.

Answer 2

There are two main ways to handle subtypes, either with sparse columns by adding columns for every possible property (preferrably with check constraints to make sure only one type has values) or to create a table for the supertype and then three tables for the sub-types, each with foreign keys back to the supertype table. Then add a check constraint to ensure that only one of the three possible type columns is not null.

Personally I decide which of the two implementations to use based on how similar the subtypes are. If 90% of the columns are shared I use the sparse columns approach, if very little information is shared I use the multiple tables approach.

Answer 3

I don't understand what the issue is. This is just mixin inheritance. Why can't a Class just have an entry each both ClassCoded and ClassNumeric?

The enforcement of business rules isn't going to be done in the DB anyways, so you can easily enforce these constraints in the business layer code with special rules for Classes that have entries in both these tables.