Recently I was asked my opinion, via Twitter, on where to store the index DDL for a single database that had multiple clients with varied usage patterns. You can see the whole thread here.
It’s a great question and there were some interesting suggestions made. My approach to this scenario is kind of a hybrid of all the suggestions and comments.
I’ve had to deal with this kind of thing in the past and what I found worked best is to create a single file for each client that contains the client specific DDL. I wish I could take credit for this idea, but it wasn’t mine, it belonged to a co-worker. At first I resisted and thought it was a bad idea. I mean really, mixing DDL for more than one object in a single script just seemed wrong and goes against every fiber of my OCD organizational self. But in the end, this is what worked best in our environment.
Our initial thought was to include our index DDL with the table, but use variables to name the index objects that were specific to the client. This way the index names would never collide, but then that kind of defeated the whole purpose of different indexing strategies for different clients. Thankfully we scrapped that idea before we implemented it.
We tried creating separate files for each table that had client specific DDL in each file. That became a nightmare when it came time to deploy and maintain. We had to build logic in our pre & post deployment scripts to handle that.
Then we tried separating the index DDL files out by client, so we ended up with a bazillion index DDL files for each table. Okay, may not a bazillion, but it was a lot and it was even more of a nightmare to maintain.
We settled on the approach I mentioned earlier, one DDL file per client that held all the DDL that was specific to the client, not specific to any object. We found it was much easier to maintain and deploy. We defaulted each of our client specific DDL files to be NOT included in the build. When it came time to do a build/deploy for a specific client, we would set the option to include the client specific file in the build. We were not using continuous integration, so this may not work if that is what your shop is doing. Or it may work with just a few tweaks to your process. It did work for our situation and it worked well.
I don’t think there is a single correct answer to this question. Like almost everything else in our tech world, the answer is going to be, “it depends”. Figure out what works in your environment and then go with it. It might take a few trial and error runs to get it right, but you’ll figure out what works best over time with a little persistence.
I’d love to hear your thoughts on this.