Hello,

I'm currently desigining a modular application. Each module contains a particular set of entities/tables. Due to possible conflicts in the naming of entities I'm planning to use a different LLBLGen project for each module.

Now my question, how would you store these modules in a SQL 2005 database? I have several options and I'm looking for the pro's en con's for each solution.

• Store all modules in the same database, all using the same schema. Each table has a module prefix to avoid conflicts.

• Same as above but now each each module is stored under a different schema in the same database which SQL 2005 currently supports.

• Use a different database for each module, LLBLGen currently supports multiple catalogs.Another question, what's the main reason to use multiple schema's anyway? Can anyone give a practical example where schema's will be benefitial?

I guess there is no definitive answer to that, it probably depends on the size of the various sets and their level of independance.

I quite like the "DotNetNuke approach" where all the independant modules are responsible for running their own piece of install/uninstall script in the same db and packaged with their own DAL. But this is also because they all relate to some core tables.

If you don't need that, then the second solution is interesting as it allows finer control on the db admin side.

But this is really if you want to be able to install and maintain any arbitrary combinations of them. In such a case, you can build n scripts, and n LLBLGen projects/corresponding libs. And then it does not matter so much whether you run your scripts in a single shema or not since LLBLGen allows to select the participating entities for each generation, and you can change the connection strings on the fly.

If you don't need such flexibility, then I guess it doesn't hurt if your modules share the same DAL, and it makes it easier to maintain as long as you keep everything in hands.

First of all, thank you for your reply. Let me clarify a bit.

The application will be an ASP (Application Service Providing) application. The application can and will be used by multiple clients who own a license to use the application and 1 or more standard modules belonging to the application.

Each client gets his own database. The various modules which each client uses is variable. Depends on the licensing.

Yes, I prefer the idea of a DAL for each module. However, sometimes dependencies between a few modules are unavoidable. Not a real problem.

I assume the LLBLGenPro designer can be used to specifiy (custom) relations between entities across schema's? Will the full functionality of LLBLGenPro be available if relations are specified like this? Any drawbacks?

I noticed that the AdventureWorks SQL 2005 samples use a similar system. The schema's are bascially used as a 'namespace'. Each schema actually looks like a module.

Security on a DB level is also important. I seems it becomes easier to lock a particular DB user in one ore more modules, as you said it allows for finer control.

Any opinions?