I have a new project which I would like to find a way to use LLBL with, but I am not sure of the best way to approach it. The project requires that multiple customers with multiple databases be supported (hosted application). Each customer will have their own database, and each table within the database will have a set of core columns (ie: id, parentid, datecreated, ownedby, etc.) which will be common for all tables in all databases. However, customers will be able to add their own columns on the fly so that outside of the core columns, each customer will have a different set of columns.

So how to model this in LLBL (if LLBL is even the right tool)? Some ways I've thought of include:

1) create core entities (such as account) and then add fields dynamically at run time to the accountentity based on the fields available for that particular customer.

2) use codedom to create entities on the fly and load the entity classes into memory as required.

I am sure better minds than mine have already tackled this problem with LLBL Any suggestions?

Thanks,

Alex

Alex

Here are some thoughts on your proposed solution

However, customers will be able to add their own columns on the fly so that outside of the core columns, each customer will have a different set of columns

Customers adding their own columns on thr fly would mean that they will need priviledged access to RDBMS instance to be able to run the DML to create these addition columns.

Instead you might want to think about having all possible columns pre-created. The customer then just 'switch' them on rather then create them. This is a lot more common - SAP, Oracle applications etc do just this. Your applicatin should be less complicated as well is you use this method.

So how to model this in LLBL (if LLBL is even the right tool)? Some ways I've thought of include:

1) create core entities (such as account) and then add fields dynamically at run time to the accountentity based on the fields available for that particular customer.

2) use codedom to create entities on the fly and load the entity classes into memory as required.

I think you will find that LLBL modeling becomes a lot more simpler.

Customers adding their own columns on thr fly would mean that they will need priviledged access to RDBMS instance to be able to run the DML to create these addition columns.

Unfortuntately, that's exactly what the specs of the project require, although we will control it through the UI so that we can limit what users can and can not do. We also thought about turning off/on fields, but that would be impractical in our situation as we need to support an "infinite variety" of column needs on behalf of the customers.

alexgad wrote:

Customers adding their own columns on thr fly would mean that they will need priviledged access to RDBMS instance to be able to run the DML to create these addition columns.

Unfortuntately, that's exactly what the specs of the project require, although we will control it through the UI so that we can limit what users can and can not do. We also thought about turning off/on fields, but that would be impractical in our situation as we need to support an "infinite variety" of column needs on behalf of the customers.

One approach that you may want to take (one that we have used) is to have a CustomerData field in each table that contains XML. This way the customer can define the XML schema (through some tool you provide) however you data access code Entities doesn't have to change.

SQL Sever 2005 makes this even easier with the XML data field type.

BOb

I dont know if this would work or not, but if i were to attempt what you are doing i would prolly start researching with this...

create your entities based upon what each customer has in common in the designer.

Next, modify the persistenceInfofactory class to read EXTRA persistance info from say an xml file or something like that. Shouldn't be to hard to modify the llblgen templates to fetch some data from an xml file as well as the generated code.

And of course somewhere along the line, you will have to read prolly the same xml file and create new field objects and attach them to the fields collection of the apporiate entity.

Then access each EXTRA field by the fields[] property of the entity.

Answer wrote:

Next, modify the persistenceInfofactory class to read EXTRA persistance info from say an xml file or something like that. Shouldn't be to hard to modify the llblgen templates to fetch some data from an xml file as well as the generated code.

No, store the XML in the row in the database. This will give you one additional field in each entity. Perhaps store and XSD in a file. But, I don't think an XSD is really needed in the case since I would think you are only storing simple types.

Answer wrote:

Then access each EXTRA field by the fields[] property of the entity.

Then one of your fields will be an XMLDocument and you can use XPath/XQuery to get the data of each custom "field".

BOb

Customers adding their own columns on thr fly would mean that they will need priviledged access to RDBMS instance to be able to run the DML to create these addition columns.

Unfortuntately, that's exactly what the specs of the project require, although we will control it through the UI so that we can limit what users can and can not do. We also thought about turning off/on fields, but that would be impractical in our situation as we need to support an "infinite variety" of column needs on behalf of the customers.

Using an XML doc in a column doesnt meet the specs.

Personally i would do it exactly how you stated with a column as a XML field. However according to his specs, they want to change the db, so my stated method (assuming it works) would allow for that since its grabbing each customers extra field info at run time through a XML mapping file.

Answer wrote:

Personally i would do it exactly how you stated with a column as a XML field. However according to his specs, they want to change the db, so my stated method (assuming it works) would allow for that since its grabbing each customers extra field info at run time through a XML mapping file.

So your customer is giving you technical specs?

Your variation on it should work.

BOb

Answer wrote:

Using an XML doc in a column doesnt meet the specs.

Personally i would do it exactly how you stated with a column as a XML field. However according to his specs, they want to change the db, so my stated method (assuming it works) would allow for that since its grabbing each customers extra field info at run time through a XML mapping file.

Yes, you're right, we have to be able to create the columns in the DB and the XML field does not give us what we need. Thanks for your suggestion. Its the road we were thinking of going down, but its good to hear others think its the right way.