Multi Company DataBase

Question

Multi Company DataBase

nalmir.hugo

Old Hand

Points: 314
More actions
June 20, 2009 at 11:18 am

#384811

I have to create a information service for my clients. Informations for many different companies will be located in a only one database. I Have a companyID. Should a input this value in any table that I create? Ex. Users/Group/Notifications/Etc. As a MultiCompany DataBase I have to identify what information/user/group belongs to each Company? Any Ideias?

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29423 More actions · Answer 1

nalmir.hugo (6/20/2009)
I have to create a information service for my clients. Informations for many different companies will be located in a only one database. I Have a companyID. Should a input this value in any table that I create? Ex. Users/Group/Notifications/Etc. As a MultiCompany DataBase I have to identify what information/user/group belongs to each Company? Any Ideias?

You store your company details in a table called company with companyID as its primary key. This primary key should be used as a foreign keys in other tables which stores details abt the child companies. The primary key of these child tables can be used in other tables. Do consider level of normalization you want to go to. The more you normalize, the more consistent your data is. However, reporting becomes bit slow if u're using same server to extract reports if u're using high degree of normalization.

Again for creating users, you can divide the users at AD level and then provide privileges on accessing tables belonging to the company a particular user belongs to. Or if you're planning to create a separate table for storing users, you must have a field that stores the company ID so that u can map users as per company basis.

Pradeep Singh

nalmir.hugo Old Hand Points: 314 More actions · Answer 2

Thanks for the reply. SAP is an ERP ( Single COMPANY ) System. To make it a MultiCOMPANY ERP would you create a COMPANY TABLE and use its CompanyID as a FK in almost every table of the system or would you make in a different way?

ps. One Orange Chip Points: 29423 More actions · Answer 3

nalmir.hugo (6/20/2009)
Thanks for the reply. SAP is an ERP ( Single COMPANY ) System. To make it a MultiCOMPANY ERP would you create a COMPANY TABLE and use its CompanyID as a FK in almost every table of the system or would you make in a different way?

Yes, you can use companyID as fk in all other tables that needs to be linked. For generic tables you dont need to add the fk constraint.

Pradeep Singh

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 4

Gosh... there's just no way to say this without ticking someone off. My apologies in advance....

This doesn't sound like a trivial undertaking. It sounds like this database could be used by several companies that could grow to depend on it. In light of the questions being asked, I'd like to suggest that you or your company consider hiring (at least on a temporary basis) someone who already knows how to build a database including such intricacies as those of which you speak and a whole lot more about those which you are not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nalmir.hugo Old Hand Points: 314 More actions · Answer 5

thanks for all the replies. The reason why I used the SAP sample was to give a rough sample of a change a complex single company system should take to implement this multi company feature.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

:blink: Let me ask just one question... how are you planing to proceed when one of the customers ask you to restore the database lets say... as it was yesterday at 10:35 AM?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

nalmir.hugo Old Hand Points: 314 More actions · Answer 7

my services will not provide these features. It''s a good question in a ERP scope.

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 8

PaulB (6/21/2009)
:blink: Let me ask just one question... how are you planing to proceed when one of the customers ask you to restore the database lets say... as it was yesterday at 10:35 AM?

Heh... oh, that's so easy... you just get on the phone to all of the other customer's GM's and CFO's and explain that you're going to rollback everthing they did until 10:35 yesterday morning. I'm sure they won't have a problem with that... 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

nalmir.hugo (6/21/2009)
my services will not provide these features. It''s a good question in a ERP scope.

It's something you should consider. Remember, the companies using your server are your customers and they may have reasons that require that type of restore. If not being able to do it causes them financial problems, you (meaning your company) could be involved in litigation.

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 10

Lynn Pettis (6/21/2009)
If not being able to do it causes them financial problems, you (meaning your company) could be involved in litigation.

... not to mention a road trip and a barrage of high velocity pork chops. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95461 More actions · Answer 11

Jeff Moden (6/21/2009)
PaulB (6/21/2009)
:blink: Let me ask just one question... how are you planing to proceed when one of the customers ask you to restore the database lets say... as it was yesterday at 10:35 AM?
Heh... oh, that's so easy... you just get on the phone to all of the other customer's GM's and CFO's and explain that you're going to rollback everthing they did until 10:35 yesterday morning. I'm sure they won't have a problem with that... 😛

Even better... fire off an email to those guys informing them that you just did that.:w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

nalmir.hugo Old Hand Points: 314 More actions · Answer 12

First of all, thanks for Lynn Pettis,Jeff Moden,PaulB,ps,WayneS for your comments. All the points stressed out here are very important. As Jeff said, this is not a simple question and the restore options questions are very pertinent. We also don't want to give peoples a Hard Attack by sending them an E-mail reporting that a restore operation has been completed. Once again Thanks for all.

ChillyDBA SSCrazy Points: 2539 More actions · Answer 13

PaulB (6/21/2009)
:blink: Let me ask just one question... how are you planing to proceed when one of the customers ask you to restore the database lets say... as it was yesterday at 10:35 AM?

Ouch! ..was my first thought on this one. However, on thinking about it for a few minutes, you could still use the originally proposed approach of a CompanyID fk in every table containing company-specific data.

Each of these tables could be partitioned on CompanyID with the partitions residing on a separate filegroup(s). This should make it possible to point-in-time restore for a single company (filegroup)? With some careful scripting, adding and removing of companies would also be possible without too much pain.

I've not tried anything this complex yet, but have used the CompanyID approach successfully in the past.

Life as a DBA: Living in a box, but thinking outside of it.

Blog: www.chilledsql.com[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Andy Hughes (6/22/2009)
PaulB (6/21/2009)
:blink: Let me ask just one question... how are you planing to proceed when one of the customers ask you to restore the database lets say... as it was yesterday at 10:35 AM?
Ouch! ..was my first thought on this one. However, on thinking about it for a few minutes, you could still use the originally proposed approach of a CompanyID fk in every table containing company-specific data.
Each of these tables could be partitioned on CompanyID with the partitions residing on a separate filegroup(s). This should make it possible to point-in-time restore for a single company (filegroup)? With some careful scripting, adding and removing of companies would also be possible without too much pain.
I've not tried anything this complex yet, but have used the CompanyID approach successfully in the past.

Just remember that there is a limit to the number of table partitions you can have on a table. IIRC that limit is 1000 partitions.