locked
Can we create multiple database (instance) for every user RRS feed

  • Question

  • Hi,

    Example: I am to create a employee management system for companies. The data itself can be large for a single company over the time (let's assume it's 10,000 rows in a table). If I have 1,000 companies and they all have approx 10,000 rows then it would be 10,000,000 rows to query.

    Query: Is it possible to create database instances for every company so that every company has it's own database (physical or virtual) with it's own set of data with the same schema? How much it would be practical to deal with it from programming point of view?

    Sunday, June 17, 2012 7:28 AM

Answers

  • 1. Company/user will login and application would connect with their exclusive database. If there are 1000 companies then the application will have to deal with 1000 times lesser records that might lead to better performance.

    If you have a single table with company id as the high-order key column of all indexes, only those rows for a given company will need to be touched.  In most cases, this will probably provide similar performance as separate smaller databases/tables.  But you may have other considerations, like different recovery or availability requirements for each company, which will necessate different physical databases.

    With the local database approach, you'll need to consider the complexities of merge conflict detection when multiple users within the same company update the same data.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by Maggie Luo Sunday, June 24, 2012 1:28 PM
    Sunday, June 17, 2012 1:31 PM
    Answerer

All replies

  • It is possible but then you will need to maintain all those databases.. 10 million rows is not big nowadays, why do you concern about having one database for all companies?

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Sunday, June 17, 2012 7:32 AM
  • Thank you for your reply...

    I was considering the following points...

    1. Company/user will login and application would connect with their exclusive database. If there are 1000 companies then the application will have to deal with 1000 times lesser records that might lead to better performance.

    2. I am also considering to have a desktop application and the local database for every company that'll sync with the online database. For one company, the data of other companies are irrelevant.

      Please guide me to the right direction.

    Thank you for your kind help.

    Sunday, June 17, 2012 1:01 PM
  • 1. Company/user will login and application would connect with their exclusive database. If there are 1000 companies then the application will have to deal with 1000 times lesser records that might lead to better performance.

    If you have a single table with company id as the high-order key column of all indexes, only those rows for a given company will need to be touched.  In most cases, this will probably provide similar performance as separate smaller databases/tables.  But you may have other considerations, like different recovery or availability requirements for each company, which will necessate different physical databases.

    With the local database approach, you'll need to consider the complexities of merge conflict detection when multiple users within the same company update the same data.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by Maggie Luo Sunday, June 24, 2012 1:28 PM
    Sunday, June 17, 2012 1:31 PM
    Answerer