Good Morning to Everyone :-)

We're having DB Design discussions in our office for a new project. Basically, we're trying to decide on a DB Solution that allows us to manage multiple online communities. We expect to have several hundred thousand users (about 100k per community), and each user will have associations to user-specific content on the community level.

At some point, we expect to have to export one or several communites into their own Datasource. This will be a downstream event that will not affect us in the immediate future.

In terms of administering the DB and each community, we're having trouble deciding between generating this DB in one inclusive datasource vs creating multiple datasources (one for each community). With that in mind, any advice or suggestions that anyone may have on this subject would be appreciated.

FYI -- We're running CF v4 and MSSQL v7.

Our concerns are as follows:

Data Structure Issues (what abilities/SQL functions will we lose when implementing multiple DB's vs one DB? We definitely have a need to be relational at every level of this application.)

DB Performance (will a one DB solution run more efficiently on MS SQL v7 with high traffic than several DB's?)

Administration (It's our understanding that multiple DB's will cause us to lose our ability to perform table joins, etc. Are there any other shortcomings that we should be aware of?)

Stability Issues (We've heard that one DB vs Multiple DB's will have fewer threads. We've also heard that MS SQL v7 becomes increasingly unstable as more threads get created. Is this true, or is our understanding of this issue wrong?)

CF Integration Issues (How will CF v4 react to handling a large amount of high-traffic DSN's vs. having one DSN and the same amount of traffic?)

Thanks in advance for all your help. We look forward to hearing from you.


Abraham Lloyd
Senior Developer