Single database or multiple database

[balachander]

I need some clarification to design the database of my project.Let me explain my project first.
This project has atleast 10 clients who are going to update the database.
Each client has 1 million records per year.Each client has similar kind of data and each client has nearly 10 tables.

I have decided to choose any of the three possible approaches for this DB design

1. Keeping a single table (with Client ID field)for all the clients(single table - single database)
2. Keeping seperate table for each client in a single database (Multiple table - single database)
3. Keeping separate database for each client (Multiple table - multiple database)


Let me know which is the best appraoach among the above three for my project.I am using SQL server 2000 as database server.Currently we have only one database server.In future, we may go for more than one database server.Even we can go for the federated database system.My query is mostly based on the client wise, but occasionally I need to query the data by combining the more than one client.
So please help me to find the best of above three approaches for the current situations which will give good performance now and the porting to federated database system can also be easily implemented in future.

Can any one help me?

Thanx in advance,
Bala

 

[tlbroadbent]

I would choose option 3 for these reasons.

1) Data access should be faster with smaller databases and tables.
2) You can easily move databases to different servers if you decide to implement more servers.
3) It is as nearly as easy to join tables in different databases as to join tables from the same database.
4) If all clients share common data, you could create a common database or replicate data across databases.

The next two items are important to me as an administrator.

5) Client security can be more easily implemented in separate databases. If you have one database and shared tables, you'd have to implement some form of row-level security which is more complex than database or table security.
6) SQL Server no longer allows recovery of single tables. An entire database must be restored. The larger databases become, the longer time required to restore. If each client has a database, recovery of a client's database will be faster and will not impact other clients.[/ol]I'm sure there are some reasons to choose the other options as well as reasons to not choose option 3. But I'll let someone else address those reasons. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.