Polymorphic Associations in .NET

Question

How to effectively / efficiently create polymorphic associations in .NET?

Having said that, I have a few more detailed questions that I would like to see as part of a broader answer.

Technology

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

Context

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

This application includes hundreds of products that correspond to various classifications. In addition, products are made up of various attributes, which may also be an attribute of their broader classifications.

Example:

"Widget A" and "Widget B" are both red, so they can be grouped in the form under "Things that are red." However, “Widget A” is a toy car, while “Widget B” is a red bike, therefore, although both are red objects, they are objects of different types. Thus, they can be grouped differently in other views (for example, "Bicycles", which will show red bicycles, blue bicycles, etc.).

goal

Create an efficient core and service level that responds to the caller and is easily maintained.

What i think to do

To easily manage all of these various attributes and relationships, I was thinking of creating a “global” attribute table where attributes could be registered for objects of different types:

GLOBAL_ATTRIBUTES_TABLE

• ID (int)
• ObjectType (int) - FK to ObjectTypes A table that contains a list of types (for example, bicycle, toy car, etc.)
• ObjectId (int) - the identifier of the object in its own table (for example, "Bicycle Table")
• AttributeType (int) - FK to AttributeTypes A table that contains various types of attributes (for example, "Color", "Material", "Age Group").
• AttributeId (int) - identifier of the attribute in its own table (for example, "Color Table")

So, columns 3 and 5 ( ObjectId and AttributeId ) should ideally have a dynamic foreign key for the table corresponding to their types.

My thinking is that this will speed up the search, building the model is simpler and less detailed (by code), adding future attributes and types of objects is easier, simplifying maintenance, etc.

Questions

• Is this an acceptable or good method for tracking (as opposed to creating, for example, product tables, grid tables, etc. with a long list of columns)?

• Is there a way to execute dynamic foreign keys / polymorphic associations in .NET by simply making a query, building a model with the results, querying this model, etc.?

• Are there any other suggestions for a better data architecture?

Answer 1

I would have an ObjectType / AttributeType relation that defines which types of attributes apply to those types of objects, and then a simple Object / Attribute model.

It should be abstract enough.

 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 2

Looking at your design, it seems that the relational model is not suitable for what you are trying to do.

Instead, I would recommend a document-oriented database , something like Raven DB or Truffler .

If you have full control over the machines the application is running on, I highly recommend elasticsearch ; it is fully distributed, replicated, and outlined and has a number of innovative features to map your data and is easy to use using HTTP and JSON (which is currently common for document-oriented databases, mainly due to the lack of a JSON scheme) .

Or, if you want to create your own, you can use Lucene.NET to do this for you (although be careful, in this case, you cannot host it in IIS, you must host it in a separate service due to domain processing applications).

In all these cases, you will save all the information related to the element in one document, so the relationships are embedded in the document.

Then you request indexes (what is stored in the documents) for certain relationships / attributes (which contain your view), usually performed using presets (note that the link is related to Lucene.NET, but similar methodologies exist in most databases, document-oriented), and it will give you everything that shares the same storyline, even for different types (something that you will need to consider of course).

Answer 3

This is indeed a fairly common problem in various forms, mainly how you maintain data on different types of things when you want to perform general operations on things regardless of type.

The easiest way to solve this problem is to have one table with all possible columns that may be required for all attributes of all types, so you consider all your objects as variants of the same type. Obviously, if you have many attributes that can become messy, you can also have columns that are hardly ever filled. The massive advantage of this is that you never have to do any joins that speed up and simplify access to the database. At the end of linq, you can get a linq request to return an object that has a factory method for the corresponding type. I would recommend you try one of two simplifications so that your attributes are reasonable.

One of them is to serialize some attributes that are not common to all objects in the string, and put them in one field. It’s good when you know for sure that you don’t have to filter your objects using these attributes, or you have to do it rarely (you can still use it with the correct “statement” that you can get in linq using .Contains) .

Another is the use of attribute-specific tables that exist only in certain types and foreign keys, to those where necessary