Setting up DBs - separate or single? 2

[MarkatTekTips2004]

I'm setting up an ASP.NET application using SQL Server 2000 which will be used by hundreds of clients. Among the tables in the database, each client should only have access to his own records.

Here's where I'm stuck...

Should I

1. Create a separate database for each client and duplicate the table set. In this case, the connection string would be slightly different based on the client logging in (i.e. the DB name will change, but that should be it). While all tables would contain a fairly small amount of records (e.g. 15,000 - 250,000), this would require several hundred databases, one for each client, on the server. Note, as clients come and go, DBs would have to be deleted / created on the fly programmatically.

or...

2. Create a single database and in each table have a field which uniquely identifies the client (e.g. clientId)? This would require that all queries include a "clientId" specification so as to retrieve/update/delete the proper rows.

I know that some tables will contain several million records in total across the hundreds of client. Others will contain perhaps only 100,000 in total.

I am unaware of the performance issues in SQL Server 2000 and wanted to get advice on this issue.

I do figure SQL Server 2000 will tackle large datasets, but is it better to keep all data within a single database with large numbers of records, or separate them as indicated above?

Thanks for any advice.

Mark

 

[hmckillop]

The option 1 would be a nightmare to maintain as not only would you need to duplicate schema changes but all procs, functions triggers etc and it could become a serious problem,

The latter option will perform ok depending on the spec of the db server, but it should easily cope with millions of records.
The most recent application i worked on was very similiar and all queries ensured they also included the client id. The only things you need to ensure is you have correct indexing i.e. you include the client id as the first field in any index which requires customer seperation and thus providing quick access to it. I would definitely recommend the second option and if you need further questions answered please post them and I will do what i can to help (as will many others on here)


"I'm living so far beyond my income that we may almost be said to be living apart

 

[MarkatTekTips2004]

makes sense and you concur with others I've heard back from. I will go with option 2.

next question...

what about primary key fields, such as the "clientId" -- is it safe to use the IDENTITY feature using AUTOINCREMENT as a setting for a PK, or is it best to generate my own and check for dups?

My concern is whether SQL Server at any point will taint the numbers used as the PK -- because as you know, these PKs are going to wind up linking into (relating into) other tables (e.g. client orders). Certainly it's vital that the clientID not get lost/changed during any regular DB maintenance or use. Any idea?

 

[hmckillop]

Firstly there wont be any issue with using an Identity field and it wont/shouldnt lose the seed value at any stage unless you specifically tell it to do so - so this shouldnt be an issue.
Secondly, its not always practical to make your Primary Key your identity field (even though SQl will make this the default PK). So you would need to research further the use of indexes/PK/Clustered Index.
An example being : I have a table Transaction, with fields id, transactiondate, SeqNum and Value. The SeqNum field relates to the order of the transaction for any one day i.e. defines uniquesness per day.
If I was to use ID as the PK and thus the clustered index, it would then physically order the data by ID. Any new IDs would be added to the end and this would seem ok. But if you consider I have millions of rows in this table and only ever search by date and order by SeqNum, it would then become more efficient to have a combination of transactionDate and SeqNum as the clustered index, especially as the transactions are also inserted in order of date. (Any search would go straight to the physically data as opposed to using the index to locate the link back to the clustered index).

So in short, no issue with using Identity's and no issue with it "tainting" them, though dont always assume that you should use them as the PK. It is key though that you research these performances issues prior and during design of your database.

hope this helps.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[MarkatTekTips2004]

Definitely helps! Thank you.

 

[ESquared]

Good work, hmckillop.

 

[hmckillop]

thanks

"I'm living so far beyond my income that we may almost be said to be living apart