Polymorphic Associations in .NET

Question

How do I effectively/efficiently create polymorphic associations in .NET?

That being said, I have a few more granular questions that I would love to see as part of the broader answer.

Technologies

• .NET 4.0
• ASP.NET MVC 3
• MS SQL 2008
• C# (latest),
• ADO.NET Entity Framework/LINQ-to-Entities

Context

I am developing a consumer-facing application consisting of a DAL, business object layer, a service broker layer (for REST services), and ultimately web, tablet, mobile, and desktop front ends.

This application involves hundreds of products that adhere to various classifications. Also, the products consist of various attributions that may also be an attribute of their broader classifications.

Example:

"Widget A" and "Widget B" both are red, so they may be grouped in a view under "Things that are red". However, "Widget A" is a toy car whereas "Widget B" is a red bicycle, so although they are both red objects they are objects of different types. As such, they may be grouped differently in other views (e.g. "Bicycles" which would show red bikes, blue bikes, etc).

Goal

Create an efficient core and service layer that is both responsive to the caller and easily maintained.

What I am thinking of doing

To easily manage all of these various attributes and relationships, I thought of building a "global" attribute table where attributes could be logged for objects of various types:

GLOBAL_ATTRIBUTES_TABLE

1. ID (int)
2. ObjectType (int) - FK to ObjectTypes Table which contains a list of types (e.g. bicycle, toy car, etc)
3. ObjectId (int) - The id of the object in it's own table (e.g. "Bicycles Table")
4. AttributeType (int) - FK to AttributeTypes Table which contains various types of attributes (e.g. "Color", "Material", "Age Group").
5. AttributeId (int) - The id of the attribute in it's own table (e.g. "Colors Table")

So, columns 3 & 5 (ObjectId and AttributeId) would ideally have a dynamic foreign key to the table that corresponds to their types.

My thinking is that this would make searching fast, model construction easy and less verbose (code-wise), adding of future attributes and object types easier, maintenance easier, etc.

Questions

1. Is this an acceptable or good method to follow (as opposed to creating, say a product table, series table, etc, with a mile long list of columns)?

2. Is there a way to accomplish dynamic foreign keys/polymorphic associations in .NET with out simply making a query, building a model with the results, querying that model, etc.?

3. Are there any other suggestions for a better data architecture?

Answer 1

Looking at your design, it seems like the relational model won't fit well for what you are trying to do.

Instead, I'd recommend a document-oriented database, something along the lines of Raven DB or Truffler.

If you have full control over the machines that the application is running on, then I highly recommend elasticsearch; it's fully distributed, replicated, and sharded, and has a number of innovative features for mapping your data and is easy to work with, using HTTP and JSON (which is common for document-oriented databases nowadays, mainly due to the schema-less nature of JSON).

Or, if you want to build your own, you can use Lucene.NET to do so for you (although be careful, in this case, you can't host it in IIS, you should host it in a separate service due to app domain recycling).

In all of these, you would store all of the information relevant to the item in a single document, so the relationships are embedded in the document.

Then, you would query the indexes (what the documents are stored in) for the particular relationships/attributes (which comprises your view), usually accomplished through fascets (note the link is Lucene.NET-specific, but similar methodologies exist in most document-oriented databases) and it will give you everything that shares the same fascet, even across different types (something you'll have to account for of course).

Answer 2

I'd have an ObjectType/AttributeType relationship which defines which attribute types are applicable to which object types, and then a simple Object/Attribute model.

This should be suitably abstract.

Object    -> ObjectType
'bicycle' -> 'vehicle'
'toy car' -> 'toy'

Attribute -> AttributeType
'red'     -> 'colour'
'39'      -> 'age'

ObjectType -> AttributeType
'vehicle' has a 'color'
'toy'     has a 'color'
'person'  has a 'age'
-- etc

Object    ->  Attribute
'bicycle' is 'red'
'toy car' is 'orange'
'Grandma' is '105'
-- etc

-- get the object types that have a color
select name from objectType where objectTypeId in
(select objectTypeId from objectTypeAttributeType where attribute = 'color')

-- get the objects that have a color
select name from object where objectTypeId in
(select objectTypeId from objectTypeAttributeType where attribute = 'color')

-- get the objects that have a color that is red
select name from object join attribute
where attribute.color = 'red'
and objectTypeId in
(select objectTypeId from objectTypeAttributeType where attribute = 'color')

Answer 3

This is really quite a common issue in various forms essentially, how do you maintain data about different types of things when you want to perform common operations on the things regardless of type.

The simplest way to solve this is to have one table with all the possible columns you might need for all the attributes of all the types, so you consider all your objects to be variations of the same type. Obviously if you have many attributes that can get messy, also you might be having columns that are hardly ever filled in. The massive advantage of this is you never have to do any joins which speeds up and simplifies your database access. On the linq end, you can get your linq query to return an object which has a method which is a factory for the appropriate type. I'd recommend you try to apply one of two simplifications to get your attributes sensible.

One is to serialise some of the attributes that are not common to all the objects in a string and put this in one field. This is good where you know for sure you won't have to filter your objects using those attributes, or you will only have to do this rarely (its still possible using the right 'like' statement which you can get in linq using .Contains).

Another is to use tables specific to the attributes which only exist in certain types and foreign key link to those where necessary