I'm creating a system that enables front-clients to define their very own business objects. Determining a company object involves creating data fields for your business object after which relevant it with other business objects within the system - fairly easy stuff. My real question is, what's the most effective storage strategy?

The needs are:

  • Must support business objects with potentially 100+ fields (of common data types)
  • The machine will ultimately support 100s of 1000's of economic object instances
  • Business objects sometimes display data and aggregates using their associations along with other business objects
  • Customers must have the ability to look for business objects by their data fields (and fields from related business objects)

The 2 possible solutions I'm able to envisage are:

  • Possess a dynamic schema so that whenever a start up business object type is produced a brand new table is produced for storing cases of that object. The object's fields become posts within the storage table.
  • Possess a fixed schema where instance data fields are saved as rows in essentially a large lengthy table.

I can tell benefits and drawbacks to both approaches:

  • the dynamic schema enables me to index search posts
  • the dynamic tables are potentially limited wide through the max column size
  • dynamic schemas eliminate / cause difficulties with replication
  • the static schema means less as well as no dynamic sql generation
  • my prediction may be the static schema may perform just like a dog if this involves searching across 100,000+ objects

What exactly is the greatest soution? Can there be another approach I've not considered?

Edit: The necessity I've been given would be to develop a generic system able to support front-consumer defined business objects. There'll obviously be limitations how these objects could be built and related, however the requirement is not up for settlement.

My client is really a company as well as a diploma of versatility in maintenance their very own clients, hence the necessity to create business objects.

I believe your condition matches perfectly to some graph database like Neo4j, as it is designed for the asked for type of versatility right from the start. It stores data as nodes and associations/edges, and both nodes and associations holds arbitrary qualities (inside a key/value fashion). One important impact on a RDBMS is the fact that a graph database will not have to research the associations inside a large lengthy table (as with your fixed schema solution), so there must be a substantial performance gain there. You are able to discover about language bindings for Neo4j within the wiki and browse what others say about this in this stackoverflow thread. Disclaimer: I am area of the Neo4j team.

With little knowledge of your circumstances...

Rather than writing an over-all purpose one-size-fits-all business objects system (the ultimate goal for Oracle, Microsoft, SAS, etc.), why don't you get it done the normal way, in which the needs are collected, along with a developer designs and implements the users' business objects within an effective manner?

In case your customers are normal, they'll produce a monster, that will finish up not operating properly, and they'll hate it. Most customers will see the data being an Stand out sheet, and never understand associations like: parent/child. Consequently you will see some crazy objects built, and impossible-to-solve reviews. You'll have to create scripts to by hand convert many old objects to higher and correctly defined ones, etc...

Your needs seem a bit as an associative database having a front-end to compose and edit organizations.

To be sure with KM above, unless of course you've got a very compelling reason to not, you'd be best utilizing a traditional approach. You will find lots of development tools and practices where you can develop a robust and scalable system. Otherwise you'll have to implement point about this yourself.