Search code examples
.netsqllinqpolymorphic-associationsentities

Polymorphic Associations in .NET

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?

Solution

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