SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Structure of the database in a global scenario.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cristianotestai
Starting Member

Brazil
2 Posts

Posted - 01/17/2013 :  15:30:45  Show Profile  Reply with Quote
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.

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?
    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.

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

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/17/2013 :  20:18:52  Show Profile  Reply with Quote
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

Go to Top of Page

cristianotestai
Starting Member

Brazil
2 Posts

Posted - 01/22/2013 :  06:11:54  Show Profile  Reply with Quote
Thanks sodeep for yours considerations!



Go to Top of Page

markspend1
Starting Member

Australia
5 Posts

Posted - 02/25/2013 :  08:41:14  Show Profile  Reply with Quote
Hey Guys well i think that the database was developed on a unique data base structure because the information kinds included were very various at the information itself showed very huge scaled samples and it was important to keep the information using a structure which allowed once to signify.Thanks!!

Edited by - markspend1 on 01/09/2014 02:08:31
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000