I have a schema that needs to allow users to "attach" up to 24 string fields to 5 different base tables and define the label text that is shown on the UI for each field.

For example, a user adds a field called "Catering" to a Check entry system. Essentially the user "picks" the UDF1 field and tells the system it should be identified with the label "Catering" on the UI. When the check is made out and the user adds Payee and Amount, the field Catering shows up on the UI as a possible data entry field. ( This is an arbitrary example )

Its certainly possible to add the 24 string fields outright to every base table. For example ID, CHECKAMT, UDF1, UDF2, UDF3 etc etc. And then provide a way for the system to remember that the user wants to show UDF1 with the title "Catering" on the UI.

Another way might be table inheritance - but it seems that I end up with 24 additional tables PER base table to describe this relationship.

Yet another possible solution is to attach and XML column to each base table and store custom data as XML in hopes that it covers the Name of the Custom Field and its Value.

Lastly there is EAV. That might be overkill since these fields I am dealing with are simple string types. Here is a nice post on EAV that explains it better than I could http://weblogs.sqlteam.com/davidm/articles/12117.aspx

So that brings me to my question. While there are any number of solutions on the internet, is there a specific approach that works best w LLBLGEN ?

Thanks in advance !

etechpartner wrote:

So that brings me to my question. While there are any number of solutions on the internet, is there a specific approach that works best w LLBLGEN ?

LLBLGen supports whatever you choose among that options: inheritance, xml or EAV. The thing is "What is the best approach for you?".

The article you posted clearly demonizes EAV, because it's very complex to get it done in a strictly way (type checking, etc). Anyway you can use it in LLBLGen. You should do your own programming rules to make sure you are inserting the right thing in the right place. LLBLGen offers you Validators, which might help you on validating the values inserted in the AttributeValue field.

A lot of people I know are using XML, because the reasons you already know. An xml field is treated either as byte[] or string in the LLBLGen generated code. Of course you have to do the xpaht queries, interpretation, etc.

Using the inheritance is still valid, and LLBLGen can handle it. But you should be careful about inheritance: you shouldn't over-use it if not necessary, read about Inheritance: pitfalls and limitations.

Bottom line: you should make an architectural decision by yourself. Anything you choose it's supported by LLBLGen.

Thanks David. How do you get from a field that has xml data stored to representing this xml as properties on an entity? Or would it hang of an entity as a collection of some sort.

Again the question I have is how does LLBLgen support this approach ( or to a lesser extend EAV) . I suspect, as you mentioned that it can be done but can you tell me how it has to be done in LLBLGen ? XML data in a column is good and well but I need to turn that into fields that can be displayed for data entry and have it in lists etc. So its almost as though I need something that dynamically alters an entity based on whats found in the xml column ?

Thanks.

For the LLBLGen's eyes the XML field is just another field. If you want to extend your entity with the data in your xml field you should do it by yourself. The best option I can think of is to use a method that traverse your xml and retrieve a Dictionary of key/value pairs. Something like:

public partial class CustomerEntity
{
     public Dictionary<string, string> AdditionalProperties
     {
          // parse the xml field and return the collection
     }
}

You can't add entity fields at runtime. You could add the xml data in a custom template to generate custom entity properties (not fields) but that won't work because the xml data might change, of course.