Now I am using another ORM and I am new to DataObjects. And I am thinking to make a switch.

I have a social modelling idea, which is based on ORM. It means every user can add his own extra properties(columns) to the original object(data) model. And it is better if one user can then share such extra properties with others. The extra properties can be saved in the same data base or saved in another one and linked togather locally, which should be decided by end user.

There is already a deep discussion, Yet another dynamic data model question… ( ... l-question). In the discuss, the participants focused on the backend, where to store the data. Their conclusion seems to be to use ESENT database, instead of a traditional RDBMS.

I need a solution for traditional RDBMS, which is better to be built on a mature ORM, such as DataObjects.

I am wondering whether DataObjects can do such a job.

My idea is not very clear. So any comments are welcome.

This thread was imported from our support forum. The original discussion may contain more detailed answer. Original topic by thinkly.

asked Apr 28 '10 at 03:36

Editor's gravatar image


One Answer:

I quickly looked up the discussion @ StackOverflow. I'd also use a hybrid EAV data model of nearly the following structure for your database (bellow is "nearly C#" notation + few DO4 attributes):

class User: {[Key] long /* or any other type */ Id, ...}
class Property: {[Key] long Id, User User, string Name, TypeCode TypeCode}
class TypeCode: {[Key] int Id, string TypeName} // the whole set of these codes can be cached on startup in RAM
interface IHasCustomProperties {} // just tagging one
class Person: IHasCustomProperties {[Key] long /* or any other type */  Id, ...}

class PropertyValue<TOwner> where TOwner: IHasCustomProperties: {
  [Key(0)] TOwner Owner, 
  [Key(1)] Property Property, 
  [Key(2)] string Index, // Reserved for collection-like properties
  byte[] Value, 
  TypeCode TypeCode

Here I used automatic open generic instance registration to provide PropertyValue<t> counterpart for each hierarchy root implementing IHasCustomProperties (in particuler, Person).

Having the following indexes on tables mapped to PropertyValue<t> type:

  • First: Owner, Property, Value, Index, includes TypeCode

  • Second: Property, Value, Index, TypeCode, includes Owner you'll be able to perform most of queries relying on these two indexes only. In fact, these indexes maintain the same data as column-oriented databases would do - although in this case the data they contain is duplicated in primary index as well.

To avoid joins with table related to e.g. Person type, you can duplicate its properties in PropertyValue<person> and use them in queries.

Obviously, byte[] Value implies some limitations:

  • You must ensure each value you store can be converted to this for, and vica verse

  • Binary-serialized value must be ordered the same way in binary order, if you'd like to run range queries on them. In most of cases this is possible, but definitely not at all the cases.

  • SQL Server 2005/2008 has 900-byte limit on index entry size - in this case it can be pretty large. So actually, this will work with SQL Server 2005 for tiny values only. Other databases (e.g. PostgreSQL) don't have this fatal lack.

  • You might consider using string instead of byte[] - at least, they're easier to deal with.

answered May 02 '10 at 18:57

Alex%20Yakunin's gravatar image

Alex Yakunin

Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA