Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Database for multi-company software

    I have to create a database for a software product used by more trading companies. Each company must have the same table schema.
    How is better to do this?
    I'm thinking for two possibilities:

    #1
    Create one table schema and in each table add a column CompanyId.
    But there will be lot of records and I think the queries will run very slow.

    #2
    Create a table schema that will duplicate for each company.
    CompaA_Users(.....)
    CompA_Products(....)
    .....................

    CompB_Users(.....)
    CompB_Products(.....)
    .............................

    Which proposal is better?
    Is there a better solution?

    Thank you.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    #1 is far better

    index your tables properly and there will be no query performance problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    #1 gets my vote too!

    Also, it is worth noting that you do not necessarily need to store the company in every table as you can limit the resultsets using appropriate joins between tables.
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    As above. Each table that has company specific data should, as mentioned, include the CompanyID column. Make that the leading column in your clustered index, and (obviously) ensure every query filters on the CompanyID. The size of your table now is largely irrelevant - each company's data is grouped together and accessible via the B-Tree.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I hadn't thought about adding it to the CI! Very good tip ++1
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Poor man's partitioning The point I never got rjberabarovovov to "get".
    Last edited by pootle flump; 01-27-09 at 04:28. Reason: remembered there was a j in there
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2009
    Posts
    2
    Thank you very much for your quick answers!

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by pootle flump
    As above. Each table that has company specific data should, as mentioned, include the CompanyID column. Make that the leading column in your clustered index, and (obviously) ensure every query filters on the CompanyID.
    Ugh! No. Use surrogate keys and avoid storing company ID at every level in your schema.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    Ugh! No. Use surrogate keys and avoid storing company ID at every level in your schema.
    Q: Show me all the products my company provide.
    A: Scaaaaaaaaaaaaaaaan.

    Q: Show me all the users in my company.
    A: Scaaaaaaaaaaaaaaaan.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Sorry poots, I'm with blindman on this. I'd rather be able to use an index on the company id in a very small table then join using index seeks on a highly selective surrogate key than to do the table scans you are advocating. If you build your queries correctly, you could use the clustered index you suggested which would allow you to at least do an index scan instead of a table scan and that could be nearly as efficient as using the surrogate keys.

    -PatP

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    What makes you think that would force a scan when a query against a clustered index wouldn't?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    Sorry poots, I'm with blindman on this. I'd rather be able to use an index on the company id in a very small table then join using index seeks on a highly selective surrogate key than to do the table scans you are advocating. If you build your queries correctly, you could use the clustered index you suggested which would allow you to at least do an index scan instead of a table scan and that could be nearly as efficient as using the surrogate keys.

    -PatP
    I'm advocating avoiding scans.

    I'll whip up some code, mebbe at the weekend - busy week. I'd like to bottom this one out since this is a default design decision I make for this sort of database.

    To be clear - I am advocating this sort of physical design in the specific circumstance where a single database is designed to store distinct and discrete data sets in the same domain. These datasets are never to be treated as one, always separately. In fact, if the data of two of these sets were to get retrieved together it would indicate either an error or a security breach.

    I am not preaching a one-size-fits-all dogma which is the de facto position of most people on the surrogate-natural key divide.

    The last two databases I released were of this nature (one a survey database, the other hosting multiple clients' data). Using this design even complex queries are jam packed full of seeks and merge joins. No lookups, scans, hash joins. Absolutely zero superfluous logical reads.

    I don't see how it isn't obvious - all "like" data is clustered with all related data. It is essentially logically & physically partitioned. Why on earth would you want to stuff a page full of 20 different clients' data when a client should only ever access their own data?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •