To have one or multiple databases, that is the question...

[mike55]

Hi all

 

I am currently working on a project whereby multiple customers could log on and use my application. Now each customer will have access to multiple pages and can insert, delete and modify data to their hearts content. Now my querie is: do I create a seperate SQL Server 2000/2005(when it comes out) for each customer, or do I put all the data into the one database and somehow prevent a customer from seeing data that does not relate to their account?

 

Mike55

[cpopham]

You should use one database for everyone. The way to prevent customers from seeing other customer's info would be to just use a select statement something like this should work:

 

SELECT *

FROM table

WHERE customerID=This customers ID;

 

If the customer ID does not match, then they will not get the info.

 

Chester

[kejpa]

That's a tricky one.

I've done both and both have their own benefits and drawbacks.

 

When it comes to security haveing many different databases where each customer has his own and only access to that one is unbeatable. But manageing the 100 databases we had wasn't fun. Our reason for separating them was that the customers demanded it. The data was sensitive and just a possibility that one client could see the others data was out of the question.

 

Many databases have some performance advantages too, but if you index properly that will not be a reason for separating them (IMHO)

 

So, the bottom line. IF you're a bummer for security do multiple databases, but prepare for a _major_ service account, otherwise stick to one big database.

 

HTH

/Kejpa

[mike55]

That's a tricky one.

I've done both and both have their own benefits and drawbacks.

 

When it comes to security haveing many different databases where each customer has his own and only access to that one is unbeatable. But manageing the 100 databases we had wasn't fun. Our reason for separating them was that the customers demanded it. The data was sensitive and just a possibility that one client could see the others data was out of the question.

 

Many databases have some performance advantages too, but if you index properly that will not be a reason for separating them (IMHO)

 

So, the bottom line. IF you're a bummer for security do multiple databases, but prepare for a _major_ service account, otherwise stick to one big database.

 

HTH

/Kejpa

 

Yea, its the actual servicing and maintance of multiple databases is worrying. But after considering both options I recon I will go with a single database per customer.

 

Mike55

[donnacha]

Are you doing a internet based web application that the public/multiple clients will have access to or is it a windows/intranet based application. If it is internet based the you will have to consider the cost of having multiple databases and what really gives you extra security in this case. Could you just have them declared with users in the database and thus restrict their access.

If you have a windows/intranet based application the you have no worries about security access as everybody belongs to the same organiation and you can still use the method of declaring them as swl users.

 

There is one other problem in that if you go with multiple databases then everwhere you access the database then it will have to be coded to cater for all possible databases. This to me would be a nightmare to be avoided.

 

I recommend one well defined database...

[kejpa]

There is one other problem in that if you go with multiple databases then everwhere you access the database then it will have to be coded to cater for all possible databases. This to me would be a nightmare to be avoided.

Of course all databases would need to have the same structure, the trouble is keeping that structure for all the databases. And yes, failing to keeping them in the same structure is a nightmare. Trust me, been there, done that, will not do the same mistakes again...

 

Still, it's the safest way to keep data separated. You have a login/pwd combination that will return your dbname in a session variable (or what you call it in .NET) and use that variable in your connection string. It's not that hard. If you make different SQL server users and revoke all rights to all databases except for their own then you're pretty sure they can't get other customers data.

It's even a setup that the customers agree on is safe, and a lot easier to explain to them compared to "We will remember to include your UserID everytime we seach the tables..."

 

But, maintenance is A LOT higher. (But you can make money out of that too ;))

 

IMHO

/Kejpa

[Joe Mamma]

hmmm . . . I am suprised no one offered this idea.

 

you can always have just one 'real' database and then each customer has their own 'vritual' database which is just views off of the main tables.

 

these virtual databases can be in seperate databases or in the same database as the main tables.

 

for example (from the master db) . . .

 

-- Main database

Create Database App

-- Main database table

create table App.dbo.AppTable1 (custid Id int)

-- Customer1 database

Create Database CustApp1

-- Customer1 database table

create view CustApp1.dbo.AppTable1 as

select * from App.dbo.AppTable1 where custid Id = 1

-- Customer2 database

Create Database CustApp2

-- Customer2 database table

create view CustApp2.dbo.AppTable1 as

select * from App.dbo.AppTable1 where custid Id = 2

 

Now, that being said. actual separate mdf files/databases allows you to rotate maintenance. you dont take everyone down at once, and your customers will be down for less time.

[rjonas]

Hi all

 

I am currently working on a project whereby multiple customers could log on and use my application. Now each customer will have access to multiple pages and can insert, delete and modify data to their hearts content. Now my querie is: do I create a seperate SQL Server 2000/2005(when it comes out) for each customer, or do I put all the data into the one database and somehow prevent a customer from seeing data that does not relate to their account?

 

Mike55

 

Are you ever going to have to understand how many customers have used certain features of your application?

 

If there's a feature that's only being used by a few customers it might not be worth developing in the next version of your application, whereas if a feature is being used by everyone it might be worth spending a lot more time on.

 

It will be easier to find this sort of information out if you have one database and a field in each table describing which customer it relates to.

 

Regards

Richard Jonas

Hertford, Herts, UK.

http://www.btinternet.com/~rjonas