I've got a project that needs user-defined characteristics for the object at runtime (Allows say an individual object within this example). The project may have a variety of customers (1000 +), each determining their own characteristics for his or her own teams of 'Person' objects.

(Eg - user #1 may have some defined characteristics, that will affect all person objects 'owned' with this user. Mutliply this by 1000 customers, and that is tha harsh truth minimum quantity of customers the application works with.) These characteristics will be employed to query the folks object and return results.

I believe fundamental essentials possible approaches I'm able to use. I'll be using C# (and then any version of .Internet 3.5 or 4), and also have a free reign re: what for a datastore. (I've mysql and mssql available, although cost nothing to make use of any software, as lengthy because it will suit you perfectly)

Have I skipped anything, or made any incorrect presumptions during my assessment?

From these options - what solution would you opt for?

  1. Hybrid EAV object model. (Define the database using normal relational model, and also have a 'property bag' table for that Person table).

    Disadvantages: many joins per / query. Poor performance. Can hit a restriction of the amount of joins / tables utilized in a question.

    I have knocked up a fast sample, which has a Subsonic 2.x 'esqe interface:

    Select().From().Where  ... etc
    

    Which creates the right joins, then filters + pivots the came back data in c#, to come back a datatable set up using the properly typed data-set.

    I haven't yet load test this solution. It's in line with the EA advice within this Microsoft whitepaper: SQL Server 2008 RTM Documents Guidelines for Semantic Data Modeling for Performance and Scalability

  2. Permit the user to dynamically create / affect the object's table at run-time. This option would be things i believe NHibernate does without anyone's knowledge when utilizing dynamic qualities, as talked about where

    http://bartreyserhove.blogspot.com/2008/02/dynamic-domain-mode-using-nhibernate.html

    Disadvantages:

    Because the system develops, the amount of posts defined can get large, and could hit the max quantity of posts. If you will find 1000 customers, each with 10 distinct characteristics for his or her 'Person' objects, then we'd require a table holding 10k posts. Not scalable within this scenario.

    I suppose I possibly could allow an individual attribute table per user, but when you will find 1000 customers to begin, that's 1000 tables as well as the other 10 odd within the application.

    I am unsure if this is scalable - however it does not appear so. Someone please correct me basically an inaccurate!

  3. Make use of a NoSQL datastore, for example CouchDb / MongoDb

    From things i read, these bankruptcies are not yet proven in massive applications, according to strings, and therefore are very at the start of development phase. Should i be incorrect within this assessment, can someone tell me?

    http://www.eflorenzano.com/blog/publish/why-couchdb-sucks/

  4. Using XML column within the people table to keep characteristics

    Disadvantages - no indexing on querying, so every column will have to be retrieved and queried to come back a resultset, leading to poor query performance.

  5. Serializing an item graph towards the database.

    Disadvantages - no indexing on querying, so every column will have to be retrieved and queried to come back a resultset, leading to poor query performance.

  6. C# bindings for berkelyDB

    From things i read here: http://www.dinosaurtech.com/2009/berkeley-db-c-bindings/

    Berkeley Db has certainly shown to be helpful, but as Robert stated – there's very difficult interface. Your whole wOO wrapper needs to be hands coded, and every one of your indices are hands maintained. It is a lot more difficult than SQL / linq-to-sql, but that’s the cost you have to pay for absurd speed.

    Appears a sizable overhead - if however anybody can offer a hyperlink to some tutorial regarding how to keep up with the indices in C# - maybe it's a goer.

  7. [EDIT - just added that one] SQL / RDF hybrid. Odd I did not think about this before. Much like option 1, but rather than an "property bag" table, just XREF to some RDF store? Querying would them involve 2 steps - query the RDF store for individuals striking the right characteristics, to come back the individual object(s), and employ the ID's of these person object within the SQL query to come back the relational data. Extra overhead, but might be a goer.

I'd really appreciate any input here!