I am just wondering is there anyone has the experince about dynamicly create database.

I am working on a research. The project is create a web management tool that allow researchers create their own website throught this management tool. We got two options for the database design now.

1. using one database to store all the researchsers' website data.
2. for each website that researchers created, we duplicate a database for it.

I just want to get some advises and options about the advantage and disadvantage for these two options.

If goes to the second option, there will be multiple database. Does hibernate can handle multiple database easily?

Thanks

Hello Heixian. I've had the unfortunate pleasure of authoring and maintaining (5 years) an application that was written in traditional ASP/MSSQL that relied on dynamically creating database. I say unfortunate because, it was just that, a very unfortunate design decision that we had to live with.

There is only one technical limitation in Microsoft SQL Server (albeit very difficult to reach), there is an artificial limitation of 32,767 databases on a single instance of sql server. I can't fathom anyone reaching this many databases, but their are consequences to pay for having a large amount of databases on the same server. You can review my discussion dialog on the MSDN forums relating to this topic (http://forums.microsoft.com/MSDN/ShowPo ... 3&SiteID=1).

Now, from a developers perspective. Unless you have purchased a tool, such as ApexSQL Diff, managing an evolving schema is going to be tricky, not impossible but it will take some patience and concentration. The application I worked on used a very simple approach to creating the database, we created a SQL file that included the "CREATE DATABASE" command and the required commands for the contained schema (tables, sprocs, functions, etc). The script had tokens that were replaced prior to execution, e.g. "CREATE DATABASE <%DatabaseName%>" (we simply stuck with the ASP stile code block tag).

Getting the software to create the databases was trivial, but we found out the hard way that trying to make changes to the schema once 3000 databases were created was next to impossible without a good tool (that supported transactions).

I would highly recommend that you go with your first option. All you need to do is take into account that each table have rows that belong to multiple users (can be abstracted in your dataaccess layer). The only disadvantages that I can see is overall database size and single point of failure. If for some reason the database becomes corrupt (malformed SQL ) you risk data loss (unless of course you have a well implemented backup plan, you do right?:)). As far as database size goes, i know each version of SQL Server (Dev, Standard & Ent) each have limitations on database size. But if you exceed the single database size you can easily scale to multiple servers or multiple databases.

Alright, that's the run down on my experiences and thoughts relating to your problem. If you have any questions, just reply.

-Beau

I forgot to mention that NHibernate only knows of your database by the Connection String that you use. For instance, I have a project that I am working on now that does not use an XML config file for NHibernate, instead i programmatically create a configuration object and set the required properties. So in theory your software could inform NHIbernate which database it needs to use and as long as the schema is in sync with your mapping files, it will be happy.

-Beau

Thanks, Beaubutton. I appreciate your relpies and sharing your experince. Your advise is a real good point and I have not thought about before.

The database of our project probably won't be that huge like yours. We are using mysql in this project. So far this application is decided for a small group of researchers to use. So in both options we don't need to worry about size of the DB or the number of DBs.

The reason we have two approach for our DB design is that:

From developers'(programmer) perspective, it is nice to have database for each website. It is better for organizing data and faster query performace than using one big database. (personlly, I don't consider this is a problem if we are using one DB option for this project) So they want to use the multiple database anc creae it on fly.

In the DB designers/DBAs side. They really do not like applications to create databases since application is used to just access the database instead of managing or organizing DB. Creating, managing and controlling databases should throught the DBMS. This is way they can control it. Also it seems the bigger the database the more likely that the DBA is going to want to do.

I like one DB design instead of going to create multiple DBs on the fly. But if the number of DBs is not issue in our project, then there also no disadvantage for using multiple DB approach.

Beaubutton, would you mind to give us some more advise base on your experince. I will appreciate that.

Also if anybody would like share their experince or give us some suggestions, please post it. Any suggestion will be helpful.

Thanks.

Henxian