Question

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… (http://stackoverflow.com/questions/2037 ... 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.

Answer 1

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.

Subscription:

Related questions