Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2005
    Posts
    9

    Customising a schema for multiple clients

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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!
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    One of the few occasions I might say "Have at it" with XML. Would be preferable to option 3, anyways.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2005
    Posts
    9
    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

  7. #7
    Join Date
    May 2008
    Posts
    3
    We are wrestling with a similar problem in this post: http://www.dbforums.com/showthread.php?t=1630660 .

    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.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I am confident that the fact that I said it will be sufficient to convince your team of the proper development strategy.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Sep 2005
    Posts
    9
    For anyone interested, the following MSDN article discusses multi tenanted architecture.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •