We are developing a system that will be deployed to many clients. Each client will have their own database (we are using MS SQL Server 2000 for the database).
Our problem is how to handle client specific customisations.
Some of the ideas being discussed include
1) For some tables (such as a user details table) provide an admin interface where an administrator (our business analysts) can add fields which modifies the schema directly (resulting in each client database effectively having their own custom schema).
2) Add a set of generic columns (ints, varchars, etc) to relevant tables and use config to define which of these fields are available to each client.
3) Use a generic lookup table strategy that has one table defining the custom fields and types required, and another table that stores the values of the relevant type.
Whatever happened to becoming an expert in a line of business and offering an application which is complete and follows best practices?
If I absolutely had to do this I'd give the clients the ability to create one-to-one spin-offs of the tables for storing their own data. Once you let them start mucking around with modifying the core tables you enter an administrative nightmare.
If it's not practically useful, then it's practically useless.
Then there is always the "Marie Antoinette" (let them eat cake) approach, give them an XML column and have your application treat that column like a black hole... Never, EVER go below the event horizon of an XML column!