quote:

---

Originally posted by cristianotestai

Hello,

I have a scenario and must define/redefine the structure of the database/schemas of my project, but i have some doubts what is best suited to do.

Scenario:

I have a web application that is used by hundreds of companies, which have several users per company and possess the modeling application
some entities, including: Accounts, Contacts, Opportunities, Campaigns, Activities, etc... concerning the CRM segment, beyond the Company and Users entities.

Currently my database architecture is the following in SQL Server 2008 R2:
CRM Database
    Schema "Commom":
         Tables: Companies, Users.
    Schema "Company X"*:
         Tables: Accounts, Contacts, Opportunities, Campaigns, Activities.
    Schema "Company Y"*:
        Tables: Accounts, Contacts, Opportunities, Campaigns, Activities.
  
 *That is, for every company that uses the web solution will have a schema containing tables that contain data specific to your business.

After much searching architecture that would be the most suitable for this scenario, which will have a centralized web application and may be
used by several different companies, came to me several questions about the concepts:

1) A negative factor in my view here is that for each new company'll need to have a way "automatically" create the schema and database objects
   to the new company. Another fact "bad" is that any update to a table or object (procedure, view, etc.) outside the common schema, i need
   have a way to replicate the update for all businesses schemas. Another situation i'll need to see how it will create backups of data from each
   company situated in their respective schemas.

Here is my take I wouldn't create separate schema for each company.That will headache to manage.Instead you can have company informations in Company and CompanyAddress and CompanyContact tables. This way you can propagate CompanyId to other tables so that will identify which company are you talking about.

2) On the other hand, if everything was in a single schema in the database, imagine the contacts table storing all contacts of all users of all companies?

I guess you would have CompanyId or EntityId to identify that and It would be no issue with proper indexing or Table Partitioning if you think it will grow huge. Reporting will be easier as you don't have to go hundreds of table to find informations. If this is new server for this application, I would think about drives and Memory and CPU perspective or Capacity Planning.
    Otherwise, they would have at each table to key business questions filters and separation of data, in addition to always having "selects" one where the application indicating the company.


    Another thing would be for the bkup/restore of a very large database containing information of all companies.

For very large db you could do full backup weekly and differential backup everyday and transaction log backup every 15/30 mins.You could also use compression feature.

I have these and other questions that are arising in a scenario of an application of this size and grateful to be able to discuss so i can make the best decision.
    
Best Regards,
Cristiano Testai
Brazil

---