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.

We found a thread related to this over here:
http://discuss.joelonsoftware.com/de...gn.4.319460.16

I have not been able to find much other information about this on the web so was hoping someone may have some experiences to share (or other threads you can point me at).

Thanks

1) sounds reasonable, as long as you're not dropping and re-creating on the fly and only adding to the schema.

2) Ewww

3) Double ewww

None of the offerings are ideal, but 1 is the lesser of the three evils in my mind.
It'll be nice to see what the other lot have to say!

1) Ewww

2) Double Ewww

3) Triple Ewww

Whatever happened to becoming an expert in a line of business and offering an application which is complete and follows best practices?

Sigh.

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.

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!

-PatP

One of the few occasions I might say "Have at it" with XML. Would be preferable to option 3, anyways.

George, you are in favour of a dynamic schema and against the fixed schema approaches. Any particular reason why? I want to go back to the team with justification for the ideas.

Blindman, Pat, thanks. Have not used XML in the database before - will look into it as an option.

These options are only our current thoughts, would be great to have some suggestions on how others might solve this.

Thanks

We are wrestling with a similar problem in this post: One Huge Database vs. Many Identical Databases .

blindman's comment seems to really hit the nail on the head: Let's build something that's great and that contemplates user customization, even though it means more work at the outset.

I am confident that the fact that I said it will be sufficient to convince your team of the proper development strategy.

For anyone interested, the following MSDN article discusses multi tenanted architecture.

http://msdn.microsoft.com/en-us/library/aa479086.aspx