Creating seperate databases for acquired companies - best practice

Hi All,
Our company has just acquired another company and I am in the
process of creating a database for the new company. The acquired
company is in the same area of business & require the exact same data
to be stored. However, We need to keep the data seperate between the
two companies for reporting purposes.
As I see it there are 2(?) options:
Add a CompanyID field to all tables to seperate the data. I.e. one
'big' database with several companies data in it.
Create seperate databases for each new company. I.e. many little
databases, each with the same structure.
We plan to acquire more companies in the future so this process may
well be required several times.
Our configuration is:
SQL server 2000 Standard edition running on Windows 2003 server
I realise that both options have their advantages & disadvantages. My
personal opinion is that a seperate DB for each company would be more
secure & easier to maintain.
However I would be interested to hear from any other people who have
been down this route & the experiences they gathered.nwhitehead@.besconsulting.co.uk wrote:
> Hi All,
> Our company has just acquired another company and I am in the
> process of creating a database for the new company. The acquired
> company is in the same area of business & require the exact same data
> to be stored. However, We need to keep the data seperate between the
> two companies for reporting purposes.
> As I see it there are 2(?) options:
> Add a CompanyID field to all tables to seperate the data. I.e. one
> 'big' database with several companies data in it.
> Create seperate databases for each new company. I.e. many little
> databases, each with the same structure.
> We plan to acquire more companies in the future so this process may
> well be required several times.
> Our configuration is:
> SQL server 2000 Standard edition running on Windows 2003 server
> I realise that both options have their advantages & disadvantages. My
> personal opinion is that a seperate DB for each company would be more
> secure & easier to maintain.
> However I would be interested to hear from any other people who have
> been down this route & the experiences they gathered.
>
Hi,
I'd go for option 2 with having a separate DB for each new company.
Adding a CompanyID to all the tables is in my opinion not something you
"just" do. That will most likely require quite a re-write of the
application that are using the database so I doubt that's an option.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator