2

I'm looking at a design specification of a work request object that would if implemented purely as a standard salesforce object would cause very sparse columns requiring tens of recordtypes and hundreds of heterogeneous hidden fields.

http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

I'm considering Abstracting out many of the sparse fields into child attribute-value records to be creating by deep cloning 'template' entries and viewed and updated by custom VF pages.

I should be able to correctly assign types the attribute values by giving the the AV object multiple record-types for which the correctly typed value field is the only one visible.

Has anyone tried this EAV approach on SFDC? Or have any suggestions on how to manage this sort of complexity otherwise?

2 Answers 2

2

I haven't tried this approach with Salesforce, but the biggest concern I'd have would be storage. Salesforce has a simplistic storage calculation where custom object records take up 2 kb each. If you went with an EAV approach, you'd be using up 2 kb for each of the sparse fields. Storage in Salesforce is expensive, so I'd probably prefer to use custom fields. This would also allow you to use standard page layouts and other point and click portions of Salesforce.

2
  • Agree- a sparse database table model would be pretty space intensive, and a large database on SFDC = extremely expensive. Aug 20, 2012 at 22:41
  • That's a valid concern I'd just worry that with the many variations required to use custom fields it'd be very hard to reliably maintain as a monolithic custom object.
    – Baxter
    Aug 20, 2012 at 22:47
0

Baxter, I think the thing to keep in mind is that SOQL (and the database ORM) within the APEX API are relatively limited interms of the scale of their permissible operations.

RecordTyping, Parent-Child Relationships make complex database hierarchies more manageable but not as dynamic as other available database engines.

That being said, I can't implemented an incredibly complex database structures on SFDC and SOQL.

I do know however, that parent-child relationships can only be queried in 1 depth lower (Collect all related records on that lookup to the parent). That maybe a limiting factor in a complex structure.

Also, consider if your going to have literally hundreds of relationships in a sparse table model, that there is no programatic way to manipulate the structure of your database. You can build objects out of meta-data xml and port them into an org, but you'll have to write a custom program to construct this XML.

6
  • The limitation on query depth shouldn't be an issue here as it's only one level deep. Other than needing to process the entity-value pairs at presentation-time I don't think there's anything too demanding for the DB engine.
    – Baxter
    Aug 20, 2012 at 22:34
  • Ok, that's good. See my additional comment about manipulating the database structure programmatically. Aug 20, 2012 at 22:37
  • Hmm, what do you mean by 'hundreds of relationships' surely it'd only be one between a parent entity and it's child attribute values? I did consider generating metadata xml files but was reticent to leave the system dependant on external code to generate them and a very complex object that would be almost impossible to modify though 'clicks'
    – Baxter
    Aug 20, 2012 at 22:41
  • sorry, I meant to say hundreds of columns, referring to the sparse DB structure: hundreds of heterogeneous hidden fields Aug 20, 2012 at 22:43
  • Ah yes I see, precisely the reason I'd prefer to abstract them out, It's fine to generate them once but maintaining and extending it it would surely make my admins cry.
    – Baxter
    Aug 20, 2012 at 22:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .