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