The only limitation here is that you can have one and only one content database per site collection for a web-application, so if you have 5 site collections in a web application, lets assume http://webspinnersinc as our web-application, we have the following 5 site collections
4. Marketing and
5. Purchase Department
So with the above structure in mind, you can have only one content database per site collection, so overall we can have 5 content databases for our web-application http://webspinnersinc
Since the concept is pretty clear now, we proceed to the how part, i mean Central administration never asks you to choose a content database or create a new content database, when you are actually creating a site collection, in-turn it analyzes the current database in use, which does not allow any control over the selection of content database, so what is the work-around? how do we accomplish this task? I got a fantastic solution from Michael Noel, from Network World, thanks Mike...
(Source - http://www.networkworld.com/community/node/19780)
------ Excerpts from his article ------
First create a Managed Path in SharePoint of /dept/ and then create site collections under the Managed Path, configuring those Site Collections to have their own Content Databases. So we want to have a separate content database for the site collection http://webspinnersinc/dept/HR
and a separate database for the site collection http://webspinnersinc/dept/Finance
There are two methods of getting around this. In the first method, the administrator goes to all Content Databases (SharePoint Central Admin – Application Management Tab – Content Databases) and takes the databases ‘offline’.
Note that simply taking the databases offline does not affect user functionality; it only disallows new Sites from being created in the specific Content Database. Once all Content Databases except for the one desired are taken offline, creating a new Site Collection will force that Site Collection to be created in the one you want.
If the databases aren’t created in advance, there is an even easier way to do this, by using the –createsiteinnewdb flag with the STSADM tool. The STSADM tool (located on web front-ends in the \program files\common files\microsoft shared\web server extensions\12\bin folder) is a fantastic administrative tool that performs a myriad of administration with SharePoint. In this case, it allows you to create a new Site Collection within a new Content Database. The following
Syntax illustrates one example:
stsadm -o createsiteinnewdb -url http://webspinnersinc/dept/hr -owneremail SharePoint@webspinnersinc.com -ownerlogin webspinnersinc\SPAdmin -sitetemplate sts -title “Human Resources” -databaseserver SERVER1 -databasename HR-Content-DB
In this example, a new site collection is created at the URL http://webspinnersinc/dept/hr The Site Collection Owner is set to webspinnersinc\SPAdmin and the Site Collection owner email is set to SharePoint@webspinnersinc.com. The default team site template is used to create the site (the template is simply named ‘sts’, without the quotes.) The database server is SERVER1 and the name of the content database created is HR-Content-DB.
Using this concept, you can construct a SharePoint environment that will scale quite nicely, as content is distributed across multiple databases. In addition, If you need to scale to multiple database servers, it simply involves moving content databases to the new server and updating the location in SharePoint