I have a situation where I am tasked with my team to deveop a database for extranet sites which would represent multiple clients data. Now, important thing is there are two parts of incoming data from clients as follows;
1. Standarized Data: Which is common accross all existing and upcoming clients.
2. Client Specific Data: It varies client to client and contains client specific data columns.
Now, when designing database how to cater these two requirements;
1. Should I create new database for every client's extranet database in SQL Server 2008 R2 ?
2. OR Should I create only one common database to hold all clients data?
If I create only one database to store all clients data including standarized data and non standarized data then how this would be managed?
ID,Name,Description,Code,City,client1customfield1,client1customfield2,client2customfield1,client3customfield1,client3customfield2,client3customfield3, ............ so on......
Can you please help me identify pros and cons of both type of implementations of database (means one database or new database for every new client)
Please let me know if you require further information.
Your comments and suggestions are greatly appreciated to solve this design issue.